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.