I see TRUNC(SYSDATE) in the code very often. Is this good or bad?
Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date.
But when does today starts and ends? And at the time of the writing, it is already tomorrow in Thailand.
We could argue about using CURRENT_DATE instead of sysdate, but it does not solve everything. Maybe you want your day to end at 10:30pm and initialize for the next day after some post processing. Maybe you want some components running in different timezones. Maybe you want to rollback and rollforward the date in your test systems…
Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.
No, I think that you would better store the date in a table and update it daily.
So you will have SELECT SUM(T.AMT) FROM T, TRADINGDAY WHERE T.EXPIRY_DATE>TRADINGDAY.TRADINGDAY and a daily job running UPDATE TRADEDAY SET TRADINGDAY=TRADINGDAY+1;.
You may want to consider the performance overhead of reading from a table instead of using the very performant SYSDATE function…
Maybe global application context could be a little bit better as a storage for date..
Why global application context? What’s wrong with a database table?
It has both advantages and disadvantages. The advantage of table could be that the start of the next date will be consistent across all sessions. The Context in comparison may have some performance benefits.
Maybe.
So you start a long running transaction at 2300 hours. Your code checks the date table at 0100 hours. It consistently says…
Yes Joel,that’s a problem… probably you must then select the date at the beginning of the transaction
@joel garry
that’s what
set transaction isolation level serializable
is made for ?
Not sure I buy this line of thought.
So, you want to store this in a table or a global variable: how will that differ from getting it from SYSDATE (which from a functional point of view is no different from a global variable irrespective of how it is implemented).
The argument that it could be tomorrow in Thailand does not hold. If you are looking for today’s date, you do not care what the date is in Thailand if you are somewhere else. And if you are in Thailand, SYSDATE will clearly give you today’s date in Thailand (or wherever you are).
And finally, this statement
“Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.”
What does this mean? Are you implying that SYSDATE is dependent on the result of running the transaction today? If so, please explain how? If the report/transaction needs to know when it was last successfully run, this means that you do need to store a last run date but that it a algorithm design problem, not a problem with SYSDATE.To put it another way, the problem is cutting a piece of wood and you made decision to use a hammer. That does not mean that there is a problem with the hammer, it is the right tool for some other task, just not for cutting wood.
Just realized that my comment might have come across wrong, I’m not trying to be a hardass, just trying to understand and learn what the underlying problem here since the illustrations given do not seem convincing to me.
Hi Steve,
No worries, your comment makes perfect sense and is appreciated.
The problem I was facing with SYSDATE was due to my specific application and may or may not apply in other cases.
Imagine all my transactions have an expiry date, if I use where EXPIRY_DATE >= trunc(sysdate), it may be innacurate when selecting trades from another region, so I would prefer where EXPIRY_DATE >= (SELECT BUSINESS DAY FROM BD_DATES WHERE REGION=t.REGION)
About the report, it is a bit complicated. If my report states WHERE D>var, I could still set var to the date I want even after it is run once.
Still I very much agree this is all a bit confuse and rely on the (maybe missing) requirements