This is a question I once asked myself, not long ago, after I accidentally pulled 13,000 rows away from the Customers table when omitting the WHERE clause in a DELETE statement. I restored the deleted rows from the latest backup and went to get some coffee, hoping no-one noticed the customers were temporarily gone. My mind was now set at the question I asked myself earlier. Couldn’t it be cool if there were a function to tell me that? How many more Mondays until I retire?
So I turned to the online forums for a couple of hours and looked for information. What I found was overwhelming! There were lot of user-functions for SQL server to retrieve dates. So I started
to investigate them and found them be either very ambitious or very stupid. Some of them had algorithms to return millions of unique dates [2,738 years] in seconds and some of them were painfully slow. The fastest of them used a combined technique of CROSS JOIN to create the desired date range, such as:
SELECT DATEADD(day, a.i + b.j + c.k, @LowDate) FROM ( SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 ) a, ( SELECT 0 j UNION ALL SELECT 3 UNION ALL SELECT 6 ) b, ( SELECT 0 k UNION ALL SELECT 12 UNION ALL SELECT 24 ) c WHERE DATEADD(day, a.i + b.j + c.k, @LowDate) <= @HighDate ORDER BY a.i + b.j + c.k
... and some of them had a loop that inserted one date at a time in the date range:
DECLARE @Dates TABLE (aDate DATETIME) WHILE @LowDate <= @HighDate BEGIN INSERT INTO @Dates SELECT @LowDate SET @LowDate = DATEADD(day, 1, @LowDate) END SELECT * FROM @Dates ORDER BY aDate
... until all dates were retrieved. And some even used a cursor!
Now I was excited and believed it would be possible to take the best of the two worlds and combine them. And I found a way to accomplish this! My requirements were that no cursors were allowed and the function should be set-based. Examples of how to use the function is last in this article.
First I searched the forums for which date intervals that was most sought for in these types of queries, and I found that a month was most often asked for (around 40%), such as Which is the last Wednesday in April 2007? Or which is the second Monday in August 2008?
After that, quarter-year, year and half-year were the runner ups.
At that point it hit me! A month is less than 32 days, a quarter-year is less than 128 days, a year is less than 512 days, and a half-year is less than 256 days. Why not create a function that adds days exponentially!? This way, both for small and somewhat large date ranges, I would quickly retreive the dates I want, without the overhead of having multiple CROSS JOINs expanded to hold millions of dates, and certainly much faster than inserting one row at a time.
In this case, a system based on binary numbers would be perfect, and understandable.
Date range | Max days for range | 2^{iterations} | Iterations |
Month | 31 | 32 | 5 |
Two months | 62 | 64 | 6 |
Quarter-year | 92 | 128 | 7 |
Half-year | 184 | 256 | 8 |
Year | 366 | 512 | 9 |
When it comes to speed, this function is fast enough for date ranges up to eight or ten years. For larger date ranges, a technique with multiple CROSS JOINs is a better alternative.
However, this is the function I ultimately come up with since I not very often select date ranges larger than a year or two.
CREATE FUNCTION dbo.fnSeqDates ( @LowDate DATETIME, @HighDate DATETIME ) RETURNS @Dates TABLE ( SeqDate DATETIME ) AS BEGIN DECLARE @Temp DATETIME IF @LowDate > @HighDate SELECT @Temp = @LowDate, @LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0), @HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0) ELSE SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0), @HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0) INSERT @Dates ( SeqDate ) VALUES ( @LowDate ) WHILE @@ROWCOUNT > 0 INSERT @Dates ( SeqDate ) SELECT DATEADD(dd, n.Items, d.SeqDate) FROM @Dates d CROSS JOIN ( SELECT COUNT(SeqDate) Items FROM @Dates ) n WHERE DATEADD(dd, n.Items, d.SeqDate) <= @HighDate RETURN END
How does the function work?
First of all, the header of the function is written to accept two dates, accepting the limits for the requested date range, inclusively.
CREATE FUNCTION dbo.fnSeqDates ( @LowDate DATETIME, @HighDate DATETIME )
To be able to use the date range later, we must also tell the function what to return, in this case a table:
RETURNS @Dates TABLE ( SeqDate DATETIME ) AS
Second, to be user-friendly, we must allow the user of the function to call it with the parameters shifted. A date range is a date range, even if earlier date is not set as first parameter. 6/1/2005
to 12/31/2005 must be treated the same as 12/31/2005 to 6/1/2005. But internally in the function, it is vital that the earlier date is stored in (@LowDate) and the later date is stored in (@HighDate).
DECLARE @Temp DATETIME IF @LowDate > @HighDate SELECT @Temp = @LowDate, @LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0), @HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0) ELSE SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0), @HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)
Why am I using the DATEADD and DATEDIFF functions here? They are used to remove time information! I want to be sure that only date information is used. So I set the time to 00:00:00.000 the fastest way I know.
What to do next?
We must initialize the function with a date, and I prefer the earliest date in the date range. This way the dates are stored ascending, not descending.
INSERT @Dates ( SeqDate ) VALUES ( @LowDate )
Now we have something to work with. What does all following code do?
WHILE @@ROWCOUNT > 0 INSERT @Dates ( SeqDate ) SELECT DATEADD(dd, n.Items, d.SeqDate) FROM @Dates d CROSS JOIN ( SELECT COUNT(SeqDate) Items FROM @Dates ) n WHERE DATEADD(dd, n.Items, d.SeqDate) <= @HighDate
These pieces of code is self-explaining:
INSERT @Dates ( SeqDate ) SELECT DATEADD(dd, n.Items, d.SeqDate) FROM @Dates d and SELECT COUNT(SeqDate) Items FROM @Dates
The first insert the dates into the return table and the last selects the number of dates present in the output table. But why? See following table.
Before | After insert | Iteration | |
1 | 0 | ||
2 | 1 | ||
4 | 2 | ||
8 | 3 | ||
16 | 4 | ||
32 | 5 | ||
64 | 6 | ||
128 | 7 | ||
256 | 8 | ||
512 | 9 |
The table represents the status of iterations. First we initialized the output table with 1 date (@LowDate). Then I select this date, add it with the number of already stored dates (1), insert the new date (@LowDate + 1) into the output table in the first iteration. Output table now holds the two dates (@LowDate) and (@LowDate + 1).
In the second iteration, I select the already stored dates (@LowDate) and (@LowDate + 1), increment them with the number of already stored dates (2) and insert the new dates (@LowLimit + 2) and (@LowLimit + 3) into the output table, which now holds the four dates (@LowDate), (@LowDate + 1), (@LowLimit + 2) and (@LowLimit + 3).
In the third iteration, I select the already stored dates (@LowDate), (@LowDate + 1), (@LowLimit + 2) and (@LowLimit + 3), increment them with the number of already stored dates (4) and insert the new dates (@LowLimit + 4), (@LowLimit + 5), (@LowLimit + 6) and (@LowLimit + 7) into the output table, which now holds the eight dates (@LowDate), (@LowDate + 1), (@LowLimit + 2), (@LowLimit + 3), (@LowLimit + 4), (@LowLimit + 5), (@LowLimit + 6) and (@LowLimit + 7).
This process, the iterations, continues until the number of total inserted rows equals the difference in days between (@LowDate) and (@HighDate). When (@LowDate) equals (@HighDate) there is nothing more to insert, @@ROWCOUNT equals 0 and the while statement exits.
How do I use the function?
Let’s look at the question ”How many workdays are there in August 2006?”. With my function this is now quite simple.
SELECT COUNT(*) Workdays FROM dbo.fnSeqDates('8/31/2006', '8/1/2006') WHERE DATEPART(dw, SeqDate) BETWEEN 2 AND 6
The answer seems to be 23 workdays. Note that my function does not take into account holidays. For this you must use a separate table. But it will be very easy to join my function to your holiday table tblHolidays and date hDate!
SELECT COUNT(*) Workdays FROM dbo.fnSeqDates('8/1/2006', '8/31/2006') WHERE SeqDate NOT IN (SELECT hDate FROM tblHolidays) AND DATEPART(dw, SeqDate) BETWEEN 2 AND 6
”How many workdays are there every month in 2006?”. Why restrict to one month?
SELECT MONTH(SeqDate) Month, COUNT(*) Workdays FROM dbo.fnSeqDates('1/1/2006', '12/31/2006') WHERE SeqDate NOT IN (SELECT hDate FROM tblHolidays) AND DATEPART(dw, SeqDate) BETWEEN 2 AND 6 GROUP BY MONTH(SeqDate) ORDER BY MONTH(SeqDate)
”Which are the Fridays for the first quarter 2008?”.
SELECT SeqDate Fridays FROM dbo.fnSeqDates('3/31/2008', '1/1/2008') WHERE DATEPART(dw, SeqDate) = 6
Even when 2008 is a leap year, the function is working.
”Which is the last Thursday in April 2007?”.
SELECT MAX(dt.SeqDate) LastThursday FROM dbo.fnSeqDates('4/1/2007', '4/30/2007') dt WHERE DATEPART(dw, dt.SeqDate) = 5
”Which is the second Tuesday in September 2012?”.
SELECT MIN(dt.SeqDate) SecondTuesday FROM dbo.fnSeqDates('9/1/2012', '9/30/2012') dt WHERE DATEPART(dw, dt.SeqDate) = 3 AND dt.SeqDate > '9/7/2012'
Here I use dt.SeqDate > '9/7/2012' because the first Tuesday will occur within the first seven days in month.
”Which is the last Wednesday of every month in 2006?”.
SELECT MAX(SeqDate) LastWednesday FROM dbo.fnSeqDates('1/1/2006', '12/31/2006') WHERE DATEPART(dw, SeqDate) = 4 GROUP BY MONTH(SeqDate) ORDER BY MONTH(SeqDate)
”Which are first and last day of every month in 2008?”.
SELECT MIN(SeqDate) FirstDay, MAX(SeqDate) LastDay FROM dbo.fnSeqDates('1/1/2008', '12/31/2008') GROUP BY MONTH(SeqDate) ORDER BY MONTH(SeqDate)
”Which are the paydays of every month in 2008 and 2009?”. Assuming monthly pay-day is 27th of every month and if 27th is weekend or holiday, the first weekday before that.
SELECT MAX(SeqDate) Paydays FROM dbo.fnSeqDates('1/1/2008', '12/31/2009') WHERE SeqDate NOT IN (SELECT hDate FROM tblHolidays) AND DAY(SeqDate) <= 27 AND DATEPART(dw, SeqDate) BETWEEN 2 AND 6 GROUP BY YEAR(SeqDate), MONTH(SeqDate) ORDER BY YEAR(SeqDate), MONTH(SeqDate)
And for all of you who have read all the way down here, ”How many more Mondays until I retire?”.
SELECT COUNT(SeqDate) Mondays FROM dbo.fnSeqDates('5/10/2006', '9/6/2034') WHERE DATEPART(dw, SeqDate) = 2 The answer is 1,478 mondays.
The answer is 1,478 mondays.
Peter Larsson
Helsingborg, Sweden