Today we will learn how to generate a sequence of dates. Usually “Date sequence generator” has a wide variety of use, we will touch up this in our next blog.

Before getting into generating a sequence of dates let us first generate a number sequence . Here is the example to generate a sequence from 1 to 10

DECLARE @start INT = 1
DECLARE @end INT = 10

;WITH num_seq
AS
(
 SELECT @start seq
 UNION ALL
 SELECT seq + 1
 FROM num_seq
 WHERE seq + 1 <= @end
 )
SELECT *
FROM num_seq

| SEQ |
-------
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |

Now let us expand this concept to generate a sequence of dates. Following example shows how to generate a date sequence from 01-JAN-2012 to 10-JAN-2012

DECLARE @start DATETIME = '01-JAN-2012'
DECLARE @end DATETIME = '10-JAN-2012'

;WITH date_seq
AS
(
 SELECT @start seq
 UNION ALL
 SELECT seq + 1
 FROM date_seq
 WHERE seq + 1 <= @end
 )
SELECT *
FROM date_seq

| SEQ |
-----------------------------
| January, 01 2012 00:00:00 |
| January, 02 2012 00:00:00 |
| January, 03 2012 00:00:00 |
| January, 04 2012 00:00:00 |
| January, 05 2012 00:00:00 |
| January, 06 2012 00:00:00 |
| January, 07 2012 00:00:00 |
| January, 08 2012 00:00:00 |
| January, 09 2012 00:00:00 |
| January, 10 2012 00:00:00 |

Isn’t it very simple!
Let us go a step further generate a date sequence of all Monday’s for the month of January,2012
To do this I need to get the first Monday which falls between my start and end date.

Here’s the logic to get first Monday of the given date (@start – taking this from previous example)

DECLARE @start DATETIME = '01-JAN-2012'
SELECT @start+2-DATEPART(dw,@start) Monday_dt

| MONDAY_DT |
-----------------------------
| January, 02 2012 00:00:00 |

The idea here is that DATEPART function with ‘dw’ as datepart will return week-day. DATEPART by default takes Sunday to be the first day of the week and returns an interger value 1 and for Monday it returns value as 2. Because I am interested in getting Monday, I have added an offset of +2 in my given date to nullify the DATEPART value to zero if it is Tuesday.

Something like this: @Start +2 (offset value ) – 2 (this 2 is returned if @Start is Monday)
Hence what I am left in the end is @Start [considering if it is Monday]

Let us take another example of @Start = ’01-JAN-2011’ (This date is Saturday)

DECLARE @start DATETIME = '01-JAN-2011'
SELECT @start+2-DATEPART(dw,@start) Monday_dt

| MONDAY_DT |
------------------------------
| December, 27 2010 00:00:00 |

This logic is correct, 1st of January,2011 being Saturday, this logic returns first Monday as 27th December,2011. But the point is that date sequence should always generate a date (Monday in our case) which is greater than or equal to start date. Hence I need to modify my logic to get this.

Here it is: I check if weekday is less than or equal to 2, if it is there is no need to modify the logic, if it is not I add +7 as offset to get Next Monday.

DECLARE @start DATETIME = '01-JAN-2011'
SELECT CASE
 WHEN DATEPART(dw, @Start)<=2
 THEN @start +2 -DATEPART(dw,@start)
 ELSE
 @start +2 + 7 -DATEPART(dw,@start)
 END Monday_dt

| MONDAY_DT |
-----------------------------
| January, 03 2011 00:00:00 |

Looking at the logic above for same date I am getting a Monday which is greater than start date.

Now embed this logic in the CTE to generate an anchor record and make a small modification in the recursive code to add an offset of +7 to start date

DECLARE @start DATETIME = '01-JAN-2012'
DECLARE @end DATETIME = '31-JAN-2012'

;WITH date_seq
AS
(
 SELECT CASE
 WHEN DATEPART(dw, @Start)<=2
 THEN @start +2 -DATEPART(dw,@start)
 ELSE
 @start +2 + 7 -DATEPART(dw,@start)
 END Monday_seq
 UNION ALL
 SELECT Monday_seq + 7
 FROM date_seq
 WHERE Monday_seq + 7 <= @end
 )
SELECT *
FROM date_seq

| MONDAY_SEQ |
-----------------------------
| January, 02 2012 00:00:00 |
| January, 09 2012 00:00:00 |
| January, 16 2012 00:00:00 |
| January, 23 2012 00:00:00 |
| January, 30 2012 00:00:00 |

Hope you enjoyed reading this post.

Happy Learning
Lokesh