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…