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