A colleague had an issue today where she needed to exclude records from a query who had a date in a specific column that fell on the weekend. She knew Oracle had an easy way to get this, but wasn’t sure exactly how. She was very much correct, and I suggested adding the following to her where clause:
to_char(date_column, 'DY') not in ('SAT','SUN')
A quick way to make sure that it is calculating days correctly across years:
select dt, to_char(dt, 'DY') dow
from (
select add_months(trunc(sysdate), 12*(1-rownum)) dt
from dual
connect by level <= 5
);
DT DOW ----------- --- 26-OCT-2011 WED 26-OCT-2010 TUE 26-OCT-2009 MON 26-OCT-2008 SUN 26-OCT-2007 FRI
I prefer using the 'DY' format mask instead of say 'D' or 'DAY'. This is because 'D' will return the number corresponding to the day of week, but I never remember which number corresponds to which day (even though it is logical, where 1 = SUN, 2 = MON ... 7 = SAT), so to me having the 3 letter representation is more self-documenting. I don't like 'DAY' because it returns the full name of the day (e.g. SATURDAY), but it is RPAD-ed to 9 characters, so all but WEDNESDAY has trailing spaces that you would have to include in your match (or trim off).
Another one of those tasks that is really easy once you know how to do it... 🙂
