Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How many more Mondays until I retire?

By Peter Larsson,

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

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 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
                   )
        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.

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
The answer is 1,478 mondays.

 

Peter Larsson
Helsingborg, Sweden

Total article views: 12380 | Views in the last 30 days: 19
 
Related Articles
FORUM

Insert - Exec Select statements

Insert - Exec Select statements

FORUM

Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

"function"

"function"

FORUM

DYNAMIC SELECT & INSERT QUERY HELP

DYNAMIC SELECT & INSERT QUERY HELP

SCRIPT

SQL Function to Split Comma Separated Values and Insert into Table

SQL Function to Split Comma Separated Values and Insert into Table

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones