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
