Date Generator in SQL Server using Recursive With Clause.

During one of performance analysis, identified lofty insert statements populating static tables with Date information.
As part of one of functionality, we were generating dates and applying necessary function on it within date range specified.

As part of performance improvement, need to move Date generation from Front end to database.
For generating date range and apply necessary function on it, had used Recursive with clause with termination based on Date range conditions.

It will generate dates between specified dates range and get below attribute for each and every date.

1. DATETIME
2. NEXT_DAY_DATE
3. PREV_DAY_DATE
4. QUARTER
5. YEAR
6. MONTH
7. MONTH_NAME
8. WEEK
9. WEEK_DAY
10. DATE_YYYYMMDD
11. DATE_MMYYYY
12. YEAR_YYYY
13. YEAR_YY
14. MONTH_MM
15. WEEK_WW
16. DAY_DD
17. DAY_OF_WEEK
18. DAY_OF_MONTH
19. DAY_OF_YEAR
20. START_OF_YEAR_DATE
21. END_OF_YEAR_DATE
22. NEXT_YEAR_DATE
23. START_OF_WEEK_DATE (based on @@DATEFIRST being default as SUNDAY)
24. END_OF_WEEK_DATE
25. START_OF_MONTH_DATE
26. END_OF_MONTH_DATE
27. NEXT_MONTH_DATE
28. DAYS_IN_YEAR
29. DAYS_IN_MONTH
30. IS_LEAP_DAY (Check for 29th Feb)
31. IS_LEAP_YEAR

Code base :

DECLARE @StartDateTime DATE
DECLARE @EndDateTime DATE
SET @StartDateTime = '2000-01-01'
SET @EndDateTime = '2017-12-31';
WITH DateRange
AS
SELECT
@StartDateTime AS 'DATE_TIME', --CURRENT DATE
'D' AS 'DATE_TYPE' , --DATE TYPE AS NORMAL DAY IT IS HARDCODED.
DATEADD(D, 1, @StartDateTime) AS 'NEXT_DAY_DATE', --NEXT DAY FROM CURRENT DATE BY USING DATEADD TO ADD 1.
DATEADD(D, - 1, @StartDateTime) AS 'PREV_DAY_DATE', --PREVIOUS DAY FROM CURRENT DATE BY USING DATEADD TO ADD -1.
DATEPART(Q, @StartDateTime) AS 'QUARTER', --GET QUARTER OF THE YEAR USING DATEPART FUNCTION.
DATEPART(YYYY, @StartDateTime) AS 'YEAR', --GET YEAR PART OF CURRENT DATE USING DATEPART FUNCTION
DATEPART(MM, @StartDateTime) AS 'MONTH', --GET MONTH PART OF CURRENT DATE USING DATEPART FUNCTION
DATENAME(month, @StartDateTime) AS 'MONTH_NAME' , --GET NAME OF MONTH BASED ON CURRENT DATE PASSED.
DATEPART(WK, @StartDateTime) AS 'WEEK', --GET WEEK PART OF CURRENT DATE USING DATEPART FUNCTION
DATENAME(weekday, @StartDateTime) AS 'WEEK_DAY', --GET WEEKDAY OF CURRENT DATE USING DATEPART FUNCTION
CONVERT(char(10), @StartDateTime, 112) AS 'DATE_YYYYMMDD', --GET DATE AS FORMATTED AS YYYYMMDD USING FORMAT 112
RIGHT (REPLICATE('0', 6) + CAST(DATEPART(MM, @StartDateTime) AS VARCHAR) + CAST(DATEPART(YYYY, @StartDateTime) AS VARCHAR), 6) AS 'DATE_MMYYYY', --GET DATE AS MMYYYY FORMAT.
DATEPART(YYYY, @StartDateTime) AS 'YEAR_YYYY', --GET YEAR PART FROM CURRENT DATE
SUBSTRING(CAST(DATEPART(YYYY, @StartDateTime) AS varchar), 3, 2) AS 'YEAR_YY', --GET YEAR PART AS YY FROM CURRENT DATE USING SUBSTRING FUNCTION AND DATEPART
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(mm, @StartDateTime) AS VARCHAR), 2) AS 'MONTH_MM', --GET MONTH PART AND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(WK, @StartDateTime) AS VARCHAR), 2) AS 'WEEK_WW', --GET WEEK PART AND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DD, @StartDateTime) AS VARCHAR), 2) AS 'DAY_DD', --GET DAY PART ND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
DATEPART(WK, @StartDateTime) AS 'DAY_OF_WEEK', --GET WEEK NUMBER FOR CURRENT DATE WITHIN YEAR
DATEPART(DD, @StartDateTime) AS 'DAY_OF_MONTH', --GET DAY OF MONTH FOR CURRENT DATE
DATEPART(DY, @StartDateTime) AS 'DAY_OF_YEAR', --GET DAY NUMBER OF THE YEAR BASED ON CURRENT DATE
DATEADD(yy, DATEDIFF(yy, 0, @StartDateTime), 0) AS 'START_OF_YEAR_DATE', --GET START DATE OF THE YEAR
DATEADD(yy, DATEDIFF(yy, 0, @StartDateTime) + 1, - 1) AS 'END_OF_YEAR_DATE', --GET END DATE OF THE YEAR
DATEADD(year, DATEDIFF(year, - 1, @StartDateTime), 0) AS 'NEXT_YEAR_DATE', --GET NEXT START DATE OF YEAR BASED ON CURRENT DATE
DATEADD(DD, - (CHOOSE(DATEPART(dw, @StartDateTime), 1, 2, 3, 4, 5, 6, 7) - 1), @StartDateTime) AS 'START_OF_WEEK_DATE', --ASSUMING WEEK START FROM SUNDAY, GET START DATE OF WEEK BASED ON CURRENT DATE.
DATEADD(DD, 7 - CHOOSE(DATEPART(dw, @StartDateTime), 1, 2, 3, 4, 5, 6, 7), @StartDateTime) AS 'END_OF_WEEK_DATE', --ASSUMING WEEK START FROM SUNDAY, GET END DATE OF WEEK BASED ON CURRENT DATE.
DATEADD(DAY, 1, EOMONTH(@StartDateTime, - 1)) AS 'START_OF_MONTH_DATE', --GET START OF MONTH DATE BASED ON DATE
EOMONTH(@StartDateTime) AS 'END_OF_MONTH_DATE', --GET MONTH END DATE BASED ON DATE
DATEADD(DD, 1, EOMONTH(@StartDateTime)) AS 'NEXT_MONTH_DATE', --GET START OF NEXT MONTH DATE
DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, @StartDateTime), 12, 31)) AS 'DAYS_IN_YEAR', --GET DAYS WITHIN A YEAR (365/366)
DATEPART(DD, EOMONTH(@StartDateTime)) AS 'DAYS_IN_MONTH', --GET DAYS WITHIN A MONTH
CASE WHEN SUBSTRING(CONVERT(char(10), @StartDateTime, 112), 5, 4) = '0229' THEN 'Y' ELSE 'N' END AS 'IS_LEAP_DAY', --CHECK WHETHER CURRENT DATE IS LEAP DAY (29-FEB)
CASE WHEN DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, @StartDateTime), 12, 31)) = 366 THEN 'Y' ELSE 'N' END AS 'IS_LEAP_YEAR' --CHECK WHETHER CURRENT YEAR IS LEAP YEAR.
UNION ALL
SELECT
DATEADD(D, 1, DATE_TIME) AS 'DATE_TIME',
'D' AS 'DATE_TYPE',
DATEADD(D, 1, DATEADD(D, 1, DATE_TIME)) AS 'NEXT_DAY_DATE',
DATEADD(D, - 1, DATEADD(D, 1, DATE_TIME)) AS 'PREV_DAY_DATE',
DATEPART(Q, DATEADD(D, 1, DATE_TIME)) AS 'QUARTER',
DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS 'YEAR',
DATEPART(MM, DATEADD(D, 1, DATE_TIME)) AS 'MONTH',
DATENAME(month, DATEADD(D, 1, DATE_TIME)) AS 'MONTH_NAME',
DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS 'WEEK',
DATENAME(weekday, DATEADD(D, 1, DATE_TIME)) AS 'WEEK_DAY',
CONVERT(char(10), DATEADD(D, 1, DATE_TIME), 112) AS 'DATE_YYYYMMDD',
RIGHT (REPLICATE('0', 6) + CAST(DATEPART(MM, DATEADD(D, 1, DATE_TIME)) AS VARCHAR) + CAST(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 6) AS 'DATE_MMYYYY',
DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS 'YEAR_YYYY',
SUBSTRING(CAST(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS varchar), 3, 2) AS 'YEAR_YY',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(mm, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'MONTH_MM',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'WEEK_WW',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DD, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'DAY_DD',
DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_WEEK',
DATEPART(DD, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_MONTH',
DATEPART(DY, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_YEAR',
DATEADD(yy, DATEDIFF(yy, 0, DATEADD(D, 1, DATE_TIME)), 0) AS 'START_OF_YEAR_DATE',
DATEADD(yy, DATEDIFF(yy, 0, DATEADD(D, 1, DATE_TIME)) + 1, - 1) AS 'END_OF_YEAR_DATE',
DATEADD(year, DATEDIFF(year, - 1, DATEADD(D, 1, DATE_TIME)), 0) AS 'NEXT_YEAR_DATE',
DATEADD(DD, - (CHOOSE(DATEPART(dw, DATEADD(D, 1, DATE_TIME)), 1, 2, 3, 4, 5, 6, 7) - 1), DATEADD(D, 1, DATE_TIME)) AS 'START_OF_WEEK_DATE',
DATEADD(DD, 7 - CHOOSE(DATEPART(dw, DATEADD(D, 1, DATE_TIME)), 1, 2, 3, 4, 5, 6, 7), DATEADD(D, 1, DATE_TIME)) AS 'END_OF_WEEK_DATE',
DATEADD(DAY, 1, EOMONTH(DATEADD(D, 1, DATE_TIME), - 1)) AS 'START_OF_MONTH_DATE', EOMONTH(DATEADD(D, 1, DATE_TIME)) AS 'END_OF_MONTH_DATE',
DATEADD(DD, 1, EOMONTH(DATEADD(D, 1, DATE_TIME))) AS 'NEXT_MONTH_DATE',
DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)), 12, 31)) AS 'DAYS_IN_YEAR',
DATEPART(DD, EOMONTH(DATEADD(D, 1, DATE_TIME))) AS 'DAYS_IN_MONTH',
CASE WHEN SUBSTRING(CONVERT(char(10), DATEADD(D, 1, DATE_TIME), 112), 5, 4) = '0229' THEN 'Y' ELSE 'N' END AS 'IS_LEAP_DAY',
CASE WHEN DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)), 12, 31)) = 366 THEN 'Y' ELSE 'N' END AS 'IS_LEAP_YEAR'
FROM DateRange
WHERE DATE_TIME < @EndDateTime -- **IMPORTANT** DONT CHANGE, IT MIGHT IMPACT AS NEVER ENDING SQL (AS MAXRECURSION IS SET TO 0)
)
SELECT *
FROM DateRange
OPTION (MAXRECURSION 0) --HINT ADDED TO AVOID RECURSION EXCEPTION CAUSE IN RECURSIVE WITH CLAUSE.

Expected Output:

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, SQL Server Learning and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s