Day of Week in Oracle

Sample SQL

    to_char(sysdate,'D') as NumDayOfWeek, 
    to_char(sysdate,'DY') as DayOfWeek, 
    to_char(sysdate,'Dy') as DayOfWeekLower, 
    case when to_char(sysdate,'D') = 3 then 3 else 1 end as DaysBack 
    from dual; 

Result of Query



to_char(sysdate,’D’) returns 1 for Sunday, 2 for Monday etc…

“dual” is of course the dummy table used to run a select statement when you don’t have any other table you want to use.
I needed to create an extract so on Tuesday morning, it could include data three days back (Saturday, Sunday, and Monday), and on all other days of the week, it would include data from the prior day back.


Leave a Reply