# How many more Mondays until I retire?

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. Wouldn’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

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 retreived. 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 2iterations 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
)
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
)
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
)
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.

 Action COUNT(SeqDate)  -- Before insert After insert Iteration Initialization 1 1 0 First insert 1 2 1 Second insert 2 4 2 Third insert 4 8 3 Fourth insert 8 16 4 Fifth insert 16 32 5 Sixth insert 32 64 6 Seventh insert 64 128 7 Eight insert 128 256 8 Ninth insert 256 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

Helsingborg, Sweden

Total article views: 12786 | Views in the last 30 days: 2

Related Articles
FORUM

### Insert - Exec Select statements

Insert - Exec Select statements

FORUM

### Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

### DYNAMIC SELECT & INSERT QUERY HELP

DYNAMIC SELECT & INSERT QUERY HELP

FORUM

"function"

FORUM

### Selecting from four columns in different table

select and insert

Tags