Thursday, June 11, 2015

General Initialiation Block creation for Date Variables




As part of report development developers may requires to create variables for dates.below code is the generic one.So you can able to use it easily.Once its done then needs to create REP variables.
select
EXTRACT(yesr FROM sysdate) as Current_YR,
to_char(sysdate,'Q') as CURRENT_QR,
EXTRACT(month FROM sysdate) as CURR_MONTH_NUM,
trunc(trunc(sysdate,'MM')-1,'MM') AS FIRST_DAY_OF_LAST_MONTH,
trunc(sysdate,'MM')-1 AS LAST_DAY_OF_LAST_MONTH,
last_day(add_months(trunc(sysdate),-1))+1 AS FIRST_DAY_OF_CURR_MONTH,
last_day(trunc(sysdate)) AS LAST_DAY_CURR_MONTH,
NEXT_DAY (TRUNC(SYSDATE), 'SUNDAY') - 7 AS FIRST_DAY_OF_WK ,
NEXT_DAY (TRUNC(SYSDATE), 'SUNDAY') - 14 AS FIRST_DAY_OF_PREVIOUS_WK ,
NEXT_DAY (TRUNC(SYSDATE), 'SATURDAY') AS LAST_DAY_OF_WK,
NEXT_DAY (TRUNC(SYSDATE), 'SATURDAY')-7 AS LAST_DAY_OF_PREVIOUS_WK,
 trim(to_char(sysdate-1,'Month')) || ' '||to_char(sysdate-1, 'DD')||', '||to_char(sysdate-1, 'YYYY') as PREVIOUS_DAY_VARCHAR,
 (trunc(sysdate) + 2-7) - (TO_NUMBER(TO_CHAR(trunc(sysdate), 'D'), '99') ),
  (trunc(sysdate) + 1) - (TO_NUMBER(TO_CHAR(trunc(sysdate), 'D'), '99') ),
   ADD_MONTHS(TRUNC(SYSDATE,'MM'),-2)  AS FIRST_DAY_OF_TWO_MONTHS_AGO,
   ADD_MONTHS(TRUNC(SYSDATE,'MM'),-3)  AS FIRST_DAY_OF_THREE_MONTHS_AGO,
   ADD_MONTHS(TRUNC(SYSDATE,'MM'),-4)  AS FIRST_DAY_OF_FOUR_MONTHS_AGO,
(NEXT_DAY (TRUNC(SYSDATE), 'MONDAY') - 28) AS MONDAY_THREE_WKS_AGO,
(NEXT_DAY (TRUNC(SYSDATE), 'SUNDAY') - 21) AS SUNDAY_TWO_WKS_AGO,
to_char(sysdate,'MM')||to_char(sysdate,'YYYY') AS CURR_QUAL_MONTH,
to_char(add_months(sysdate,-1),'MMYYYY') AS PREVIOUS_QUAL_MONTH,
NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') - 7 AS FIRST_DAY_OF_WK_MONDAY,
NEXT_DAY(TRUNC(SYSDATE), 'SUNDAY') AS LAST_DAY_OF_WK_SUNDAY,
trunc(trunc(sysdate,'YY')-1,'YY') AS FIRST_DAY_OF_LAST_YR
from dual;