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.

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.

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