Determine Day of Week from Date

By | October 26, 2011

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... 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

Turn on pictures to see the captcha *