Sample SQL

select 
    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

Oracle_Day_Of_Week

Explanation

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.

Filed under: Oracle PL/SQL