Sunday, 11 March 2012

Different Date Formats

Since you are going to be setting these date variables using physical SQL in initialization blocks, the SQL issued will be specific to the database platform you are using. For Oracle, you could write:
trunc(sysdate) - to_char(sysdate,'D')+1 CurrentSunday
, trunc(sysdate) - to_char(sysdate,'D')+2 CurrentMonday
, trunc(sysdate) - to_char(sysdate, 'D')+7 CurrentSaturday
, trunc(sysdate) - to_char(sysdate,'D')+8 NextSunday
, trunc(sysdate) - to_char(sysdate,'D') PreviousSaturday
, trunc(sysdate) - to_char(sysdate,'D')+2-8 PreviousSunday
, trunc(sysdate) - to_char(sysdate,'D')+2-7 PreviousMonday
, cast(to_char(trunc(sysdate), 'YYYY') as INT) CurrentYear
, Cast(to_char(trunc(sysdate), 'YYYY')-1 as INT) PreviousYear
, add_months(trunc(last_day(sysdate)),-1) + 1 CurrentMonthFirstDay
, last_day(trunc(sysdate)) CurrentMonthLastDay
, add_months(TRUNC(last_day(sysdate)),-2) + 1 PreviousMonthFirstDay
, case when last_day(SYSDATE) = SYSDATE then TRUNC(SYSDATE) else add_months(TRUNC(last_day(sysdate)),-1) end LASTDAYCOMPLETEMONTH
from dual;

If you are using a calendar that’s different from the normal “Gregorian” calendar (i.e. a fiscal calendar) that you have stored in a Periods table, you can write the analogous SQL for that calendar. You won't be able to use the Oracle date functions for many of the values you want, but you can still write the SQL to return the values according to the fiscal periods in your calendar using different methods. 

Thanks to -  KPI PARTNERS

No comments:

Post a Comment

Thanks to Comment