Code Example: Oracle SQL Query for Yesterday's Data

Do you have any queries running against "yesterday's" (i.e. the previous business day's) data where you actually have to adjust every week to account for the weekend? Here's a simple trick to make this less painful on Monday morning...


Let's say you own a smallish business that's only open Mon-Fri. You have a data pull that you get each morning that grabs sales transaction data from the previous business day.
The most basic way to do this is to modify your query each day according to the proper date, like so:

SELECT trns.trans_num
FROM transactions trns
WHERE trns.trans_date = TO_DATE('01/01/2001', 'MM/DD/YYYY')
AND trns.trans_type = 'SALE'

That's a pain to manipulate EVERY day, and requires manually running on top of that. So what if we want to automate this? You can use the following on an Oracle DB to to get the numbers for any sales for yesterday's date.

SELECT trns.trans_num
FROM transactions trns
WHERE trns.trans_date = TRUNC (SYSDATE - 1)
AND trns.trans_type = 'SALE'

SYSDATE simply returns the current system's date and time, while TRUNC() strips off the time, so you are just left with a date. Subtracting one ( - 1) takes one less than today's date, which is yesterday.

But what if yesterday was Sunday, and there were no transactions? And how do you see what happened Friday? Do you come in to work on Saturday just to get it? --- Obviously, the situation is not so dire, but this can be a pain, and I'm trying to make a point. ;-)

You can avoid changing the date using this:

SELECT trns.trans_num
FROM transactions trns
WHERE trns.trans_date = TRUNC (SYSDATE - CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'D')) = 2 THEN 3 ELSE 1 END)
AND trns.trans_type = 'SALE'

Simply, we are telling the query that if today is Monday, look at three days ago, otherwise look at 1 day ago. TO_CHAR(SYSDATE, 'D') converts the current date to an integer, representing the day of the week. In my case, Sunday = 1, Monday = 2, and so on. If you are operating in a different locale, this may be different, depending on your NLS settings. To see how this might be different for you, just run this for yourself and see what today returns:

SELECT TO_CHAR(SYSDATE, 'D')
FROM DUAL

Then use that as your basis for determining what value Monday will return.