Fun with Time and Labor Dates

There's a useful little table in PeopleSoft Time and Labor called PS_TL_DATES_TBL. This holds a variety of date information, such as day number, julian date , day of week and month, that can save a bit of coding to derive these values yourself. The following example uses this table to get the start and end dates of the previous, current and next years, months and weeks.

select DAT.THE_DATE,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as START_OF_PREVIOUS_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as END_OF_PREVIOUS_YEAR,
       TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as START_OF_CURRENT_YEAR,
       TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as END_OF_CURRENT_YEAR,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as START_OF_NEXT_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as END_OF_NEXT_YEAR,
       ADD_MONTHS((TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1),-1)                    as START_OF_PREVIOUS_MONTH,
       DAT.THE_DATE - DAT.DAYOFMONTH                                                as END_OF_PREVIOUS_MONTH,
       TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1                                     as START_OF_CURRENT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),1)                                as END_OF_CURRENT_MONTH,
       ADD_MONTHS(TRUNC((DAT.THE_DATE - DAT.DAYOFMONTH) + 1),1)                     as START_OF_NEXT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),2)                                as END_OF_NEXT_MONTH,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 13
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 8
                         when DAT.DAYOFWEEK = 4 then 9
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 11
                         when DAT.DAYOFWEEK = 7 then 12
                       end                                                          as START_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 1
                         when DAT.DAYOFWEEK = 3 then 2
                         when DAT.DAYOFWEEK = 4 then 3
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 5
                         when DAT.DAYOFWEEK = 7 then 6
                       end                                                          as END_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 6
                         when DAT.DAYOFWEEK = 2 then 0
                         when DAT.DAYOFWEEK = 3 then 1
                         when DAT.DAYOFWEEK = 4 then 2
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 4
                         when DAT.DAYOFWEEK = 7 then 5
                       end                                                          as START_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 0
                         when DAT.DAYOFWEEK = 2 then 6
                         when DAT.DAYOFWEEK = 3 then 5
                         when DAT.DAYOFWEEK = 4 then 4
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 2
                         when DAT.DAYOFWEEK = 7 then 1
                       end                                                          as END_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 1
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 6
                         when DAT.DAYOFWEEK = 4 then 5
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 3
                         when DAT.DAYOFWEEK = 7 then 2
                       end                                                          as START_OF_NEXT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 13
                         when DAT.DAYOFWEEK = 3 then 12
                         when DAT.DAYOFWEEK = 4 then 11
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 9
                         when DAT.DAYOFWEEK = 7 then 8
                       end                                                          as END_OF_NEXT_WEEK
  from PS_TL_DATES_TBL DAT
 where DAT.THE_DATE = TRUNC(SYSDATE)
 order by DAT.THE_DATE desc

What day is it son?

If you need to know what day of the week it is you could get the day number then go on the principle that 1 is a sunday, 2 is a Monday etc, to work it out. The problem with this however, is that this number can vary depending on how the Oracle environment is configured, which makes this unreliable. A better way of finding out the day is to use the 'DAY' parameter of TO_CHAR. This returns the day as MONDAY, TUESDAY etc. The only thing to watch here is that the returned value has trailing spaces, so if you're comparing it then remember to trim it first. For example: -

select DAT.THE_DATE, to_char( DAT.THE_DATE, 'DAY' )
  from PS_TL_DATES_TBL DAT
where trim(to_char( DAT.THE_DATE, 'DAY' )) in ('SATURDAY','SUNDAY')
  and DAT.THE_DATE between '01-DEC-2011' and '31-DEC-2011'
order by DAT.THE_DATE Asc

Enjoy.