Playing with DATE ranges

Playing with DATE ranges

If you search on the net for a trick to generate date rangees in Oracle, you most probably will end up here:

http://stackoverflow.com/questions/418318/generate-a-range-of-dates-using-sql

variuos interesting and mind-challenging ways to achieve the same result.

I found this way the most cost effective and readable, yet flexible enough for my datawarehouse ETL processes.

WITH LIMITS AS (SELECT TO_DATE (’01-JAN-2010′) BEGINDATE, SYSDATE + 10 ENDDATE FROM DUAL)

SELECT (BEGINDATE + LEVEL – 1) AS DATES

FROM LIMITS

CONNECT BY LEVEL <= (ENDDATE – BEGINDATE)

/

You can modify start and end date and have a calendar in one SQL pass. Let’s go a little further: I always have reports wich need to show the current week (WTD) for example:

WITH LIMITS AS (SELECT TRUNC ( SYSDATE, ‘DY’) BEGINDATE, TRUNC (SYSDATE) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

Easy way to retrieve a list of days in the current week until today. Fast, cost effective and easy to modify. And so on:

–Last full Week

WITH LIMITS AS (SELECT TRUNC ( SYSDATE – 7, ‘DY’) BEGINDATE, TRUNC ( SYSDATE, ‘DY’) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

–Same day last Week to Date

WITH LIMITS AS (SELECT TRUNC (SYSDATE – 6) BEGINDATE, TRUNC (SYSDATE) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE + 1))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

–Last Month

WITH LIMITS AS (SELECT TRUNC ( ADD_MONTHS ( SYSDATE, -1), ‘MONTH’) BEGINDATE, TRUNC (SYSDATE,’MONTH’) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

–Year to Date

WITH LIMITS AS (SELECT TRUNC ( SYSDATE, ‘YEAR’) BEGINDATE, TRUNC (SYSDATE) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE + 1))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

If you need to build a time dimension for your datawarehouse, you may probably need more that that.

WITH LIMITS AS (SELECT TRUNC ( SYSDATE, ‘YEAR’) BEGINDATE, TRUNC (SYSDATE) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE + 1))

SELECT DATES

      ,TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

      ,DATES – 7 ONE_WEEK_AGO

      ,ADD_MONTHS ( DATES, -1) ONE_MONTH_AGO

      ,ADD_MONTHS ( DATES, -12) ONE_YEAR_AGO

      ,TRUNC ( DATES, ‘Q’) BEGIN_OF_QUARTER

      ,TRUNC ( ADD_MONTHS ( DATES, 3), ‘Q’) – 1 END_OF_QUARTER

  FROM CALENDAR

/

Check out http://programmerslounge.blogspot.com/2011/07/how-to-get-first-day-and-last-day-of.html for a list of more first/last attributes.

Leave a Reply

Leave a Reply