SQLServerCentral / Discuss Content Posted by Peter Larsson / Article Discussions / Article Discussions by Author / How many more Mondays until I retire? / Latest Posts

RE: How many more Mondays until I retire?

SwePeso — Wed, 17 Jun 2009 15:48:14 GMT

I have posted a new function on my blog.The function can compute the Nth weekday in a month, either from the beginning or from the end of month.[url=http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx]http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx[/url]You can try it out and comment it.

RE: How many more Mondays until I retire?

SwePeso — Tue, 11 Dec 2007 03:03:39 GMT

Thank you for your kind response.

RE: How many more Mondays until I retire?

Jwalant Natvarlal Soneji — Mon, 10 Dec 2007 23:19:53 GMT

Great code!I am fan of yours!I will try to use the code without creating a function. I guess, I am very much comfortable with long queries :hehe:.Thanks.

RE: How many more Mondays until I retire?

Michael Meierruth — Fri, 21 Jul 2006 10:56:00 GMT

The topic is very similar to a recent 'Users that need help' query in SQLServerCentral.com. He asked how many workdays (mon-fri) are there between two given dates. And what's interesting is that I too recently was hit with this problem only to find the same weird stuff on the Internet that Peter Larson mentioned. And I too decided to hack this problem on my own.

Whereas Peter Larson decided to take a general approach, I decided to take an approach that answers just that question. Thus my function looks as follows (it may need to take advantage of the code he uses that takes strips the time portion of a date):

create function trx_workdays(@p_startdate datetime, @p_enddate datetime) returns integer asbegin

declare @dwdest intdeclare @padded_enddate datetimedeclare @padded_workdays intdeclare @dw intdeclare @diff int

if @p_startdate is null or @p_enddate is null return 0

set @padded_enddate=@p_enddateset @padded_workdays=0

-- pad end date so that difference becomes a multiple of 7 days;-- we also need to count the number of weekdays in the days we added for the padding;set @dwdest=datepart(dw,@p_startdate)-1if @dwdest=0 set @dwdest=7while datepart(dw,@padded_enddate)<>@dwdest -- loops no more than 6 times begin set @padded_enddate=dateadd(d,1,@padded_enddate) set @dw=datepart(dw,@padded_enddate) -- 1=saturday 7=sunday if @dw<>1 and @dw<>7 set @padded_workdays=@padded_workdays+1 end

set @diff=datediff(d,@p_startdate,@padded_enddate)+1-- subtract number of intervening saturdays/sundays and subtract number of workdays we added for the paddingreturn @diff-@diff/7*2-@padded_workdays

endgo

When I have some time I will benchmark this against Peter Larson's and other solutions.

RE: How many more Mondays until I retire?

SwePeso — Fri, 21 Jul 2006 02:41:00 GMT

Yes, they are equally safe.

The DATEDIFF(day, 0, GETDATE()) calculates the number of days passed since day Zero, which is January 1, 1900. This is what clips the time information.

These number of days I then add to the day Zero with DATEADD

DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

The red part calculates the number of days passed since day Zero and the green part then adds this number of days to day Zero, which gives us the day we started with, and the time information clipped.

This is convenient since I don't convert datetime into any other data type.

RE: How many more Mondays until I retire?

Fal — Thu, 20 Jul 2006 18:53:00 GMT

I use float as this is the same underlying data structure used to store datetime datatypes (something I picked up from SQLServerCentral but I have no supporting documentation so that's my caveat.) By using the same data structure I try to avoid datatype conversion processing overheads, such as with the extremely common 'varchar' method, ie: convert(datetime, convert(varchar(8), getdate(), 112)), or errors as with 'int' where it rounds up.

I put the datediff, float and varchar methods through the wrangler twice, and got:

iteration	datediff	float	varchar	iteration	datediff	float	varchar
1	0	0	0	1	0	0	0
10	0	0	0	10	0	0	0
100	0	0	1	100	0	0	2
1000	6	7	12	1000	4	7	13
10000	57	70	133	10000	71	75	137
100000	684	772	1376	100000	649	700	1355

'Varchar' is an appalling choice for speed, though 'datediff' does hold a slight advantage over 'float'.

However, my main reason for raising this was (subjective) readability: it was not initially clear to me what DATEDIFF(day, 0, getdate()) was trying to accomplish by passing "0" as a date. I'm also a bit wary of implicit conversions, so how "safe" is 0 as a default date? Can the default be changed from 1-1-1900, and what effects would this have?

So, with a whole 51 millisecond difference for 100,000 "time strips" (or about 274 years for a date range function), which is the *safest* method? Or are they equally safe and we should all use 'datediff' for the slight efficiency gained?

RE: How many more Mondays until I retire?

SwePeso — Thu, 20 Jul 2006 03:50:00 GMT

Yes, R2ro's solution is fast. And if I change

SET @daysDiff = DATEDIFF(day, @prmLoDate, @prmHiDate)

SET @daysDiff = 1 + DATEDIFF(day, @prmLoDate, @prmHiDate)

it calculates the right days too.

I tried with the daterange 20060101 to 20060102 (Jan 1 to Jan 2, 2006) and it only gave me the first date.

I think it is good that we have these after discussions. My idea with the article was to give an idea of how to implement a function that gives a daterange on the fly. Of course having a permanent tally table is faster. And it would be better if the counter arguments given are fully tested.

RE: How many more Mondays until I retire?

SwePeso — Thu, 20 Jul 2006 03:33:00 GMT

Good! Here is a guy who actually read the first part of the article where I wrote that a CROSS JOIN solution is the fastest solution

RE: How many more Mondays until I retire?

Jesper-244176 — Thu, 20 Jul 2006 02:35:00 GMT

I believe the following function is faster than all the above suggested:

CREATE FUNCTION dbo.fnSeqDates( @LowDate DATETIME, @HighDate DATETIME)RETURNS @Dates TABLE ( SeqDate DATETIME )ASBEGIN

DECLARE @Temp DATETIME DECLARE @NumberOfDays int

IF @LowDate > @HighDate SELECT @Temp = @LowDate, @LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate) - 1, 0), @HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0) ELSE SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate) - 1, 0), @HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)

SELECT @NumberOfDays = DATEDIFF(d, @LowDate, @HighDate)

INSERT @Dates SELECT DATEADD(d, Number, @LowDate) AS Date FROM Numbers WHERE Number <= @NumberOfDays

RETURNENDGO

It uses a Numbers table, containing all Numbers less than, say 1000000. This can be created once and for all as follows:

SELECT TOP 1000000 Number = IDENTITY(INT, 1, 1) INTO NumbersFROM sysobjects a1 CROSS JOIN sysobjects a2 CROSS JOIN sysobjects a3 CROSS JOIN sysobjects a4 CROSS JOIN sysobjects a5

ALTER TABLE Numbers ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(Number)GO

RE: How many more Mondays until I retire?

Razvan Socol — Thu, 20 Jul 2006 02:05:00 GMT

Peter, the following code:select cast(cast(getdate() as int) as datetime)will give incorrect results if the time is in the afternoon.

However, the code that Simon wrote:select cast(floor(cast(getdate() as float)) as datetime)gives the correct result.

Razvan

RE: How many more Mondays until I retire?

SwePeso — Thu, 20 Jul 2006 00:47:00 GMT

This is an interesting approach!

However, since float is not exact value there might be a difference in result. But since we only want to keep day information that might not be a problem.

If you have the time, run your test-code again with this code

select cast(cast(getdate() as int) as datetime)

RE: How many more Mondays until I retire?

Razvan Socol — Thu, 20 Jul 2006 00:28:00 GMT

Nice work, R2ro! It seems that your function is the best, so far. Here are some timings (in ms) for the three versions (counting the rows in the result, for the dates between Jan 1, 2006 and Jan 31, 2307):

Peter	Razvan	R2ro
1906	1670	766
2076	2846	796
830	1653	876
906	610	766
860	1703	873
3170	2846	703

As you can see, on my system, the timings are varying strangely, but on average, your method seems to be the best.

Razvan

RE: How many more Mondays until I retire?

Fal — Wed, 19 Jul 2006 18:54:00 GMT

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.

[ie: DATEADD(day, DATEDIFF(day, 0, getdate()), 0) ]

I've not seen this method used before. Although it pops up from time-to-time on this site, the "best" method I knew for stripping the time was:

cast(floor(cast(getdate() as float)) as datetime)

After some testing (with a rather clunky while loop) I got the following table:

iterations datediff floor
1 0 0
10 0 0
100 0 0
1000 7 6
10000 68 75
100000 712 810

Iterations is the number of times the method was used, run 100 times each, and then an average taken. Seems to be that if you're performing a low number of "time strips" there is negligible difference. Indeed, for 100,000 iterations there is only a 98 millisecond gain. Less than 1 second.

So, *is* there a real difference of one method over the other?

RE: How many more Mondays until I retire?

R2ro — Wed, 19 Jul 2006 12:51:00 GMT

I too wrote a slightly different version... Same binary concept, implemented slightly differently with a twist! It allows the specification of an increment or step value.

CREATE FUNCTION dbo.udf_genDateSequence   (      @prmLoDate datetime, -- lower date boundary (starting value)      @prmHiDate datetime, -- upper date boundary (maximum value)      @prmIncDays int      -- increment value   )RETURNS @Dates TABLE (DateVal datetime NOT NULL PRIMARY KEY)AS/*
Function:   Generate a table of dates.
Strategy:   Set-based scalar value generation.
Usage:      dbo.udf_genDateSequence(fromValue, toValue, stepValue)
*/
BEGIN   DECLARE      @daysDiff int,      @swapVar datetime 
 
   IF @prmLoDate > @prmHiDate         BEGIN            SET @swapVar = @prmLoDate            SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0)            SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @swapVar), 0)         END      ELSE         BEGIN            SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmLoDate), 0)            SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0)         END
 
   SET @daysDiff = DATEDIFF(day, @prmLoDate, @prmHiDate)
 
   INSERT INTO @Dates VALUES(@prmLoDate)
 
   WHILE @prmIncDays <= @daysDiff      BEGIN         INSERT INTO @Dates            SELECT DATEADD(day, @prmIncDays, DateVal)               FROM @Dates               WHERE DateVal <= DATEADD(day, -@prmIncDays, @prmHiDate)
 
         SET @prmIncDays = @prmIncDays * 2      END   RETURNENDgo

RE: How many more Mondays until I retire?

Richard Thomas-189729 — Wed, 19 Jul 2006 09:26:00 GMT

I had the idea of populating the Holiday table myself this morning. I found a function to calculate the date for Easter on this page:

http://www.databasejournal.com/scripts/article.php/3469911

Then I used that along with Peter's seqDates function to create the following SP. Covers all UK Holidays, and if New Year, Christmas or Boxing Day occur at the weekend it inserts the appropriate Monday/Tuesday into the table.

CREATE PROCEDURE GenerateHolidays (@yr SMALLINT)

DECLARE @tmpdatec VARCHAR(10)DECLARE @tmpdate SMALLDATETIMEDECLARE @tmpdesc VARCHAR(50)DECLARE @sdate VARCHAR(10)DECLARE @edate VARCHAR(10)

SET @tmpdatec = '01/01/' + CAST(@yr AS VARCHAR)SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate) IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate) SET @tmpdesc = 'New Year''s Day'INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = dbo.fnYear2Easter(@yr)SELECT @tmpdate = DATEADD(DAY, -2, @tmpdate)SELECT @tmpdesc = 'Good Friday'INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = DATEADD(DAY, 3, @tmpdate)SELECT @tmpdesc = 'Easter Monday'INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @sdate = '05/01/' + CAST(@yr AS VARCHAR)SET @edate = '05/31/' + CAST(@yr AS VARCHAR)

SELECT @tmpdate = MIN(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2SET @tmpdesc = 'May Day Bank Holiday'INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = MAX(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2SET @tmpdesc = 'Spring Bank Holiday'INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @sdate = '08/01/' + CAST(@yr AS VARCHAR)SET @edate = '08/31/' + CAST(@yr AS VARCHAR)

SELECT @tmpdate = MAX(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2SET @tmpdesc = 'August Bank Holiday'INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @tmpdatec = '12/25/' + CAST(@yr AS VARCHAR)SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate) IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate) SET @tmpdesc = 'Christmas Day'INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @tmpdatec = '12/26/' + CAST(@yr AS VARCHAR)SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 2, @tmpdate) IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 2, @tmpdate) SET @tmpdesc = 'Boxing Day'INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

RE: How many more Mondays until I retire?

SwePeso — Wed, 19 Jul 2006 09:13:00 GMT

Yes, I did benchmark your code Razvan. Did you try your test-code yourself?Running your test-code above gives 763 milliseconds for my function and 1,563 milliseconds for your function.

Mine is faster and I think that is because SQL is not very fast handling two variables as in your function.

RE: How many more Mondays until I retire?

SwePeso — Wed, 19 Jul 2006 09:04:00 GMT

The best way would be to use this function

CREATE FUNCTION dbo.GetEasterSunday ( @Y INT ) RETURNS SMALLDATETIME AS BEGIN DECLARE @EpactCalc INT, @PaschalDaysCalc INT, @NumOfDaysToSunday INT, @EasterMonth INT, @EasterDay INT SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30 SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28) SET @NumOfDaysToSunday = @PaschalDaysCalc - ((@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7) SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44 SET @EasterDay = @NumOfDaysToSunday + 28 - (31 * (@EasterMonth / 4)) RETURN ( SELECT CONVERT ( SMALLDATETIME, RTRIM(@Y) + RIGHT('0'+RTRIM(@EasterMonth), 2) + RIGHT('0'+RTRIM(@EasterDay), 2) ) ) END

Taken from http://www.aspfaq.com/show.asp?id=2519

RE: How many more Mondays until I retire?

John Rempel — Wed, 19 Jul 2006 08:49:00 GMT

Well done Peter. I like the versatility. Great examples.You could even populate the holiday table this way, at least when they are defined like the Civic Holiday or Labour Day (in Canada) as the first Monday in August and September, respectively. For holidays dependant on the lunar cycle, like Easter... whatcha gonna do?

RE: How many more Mondays until I retire?

Razvan Socol — Wed, 19 Jul 2006 08:43:00 GMT

I modified your function as follows:

CREATE FUNCTION dbo.MySeqDates (@LowDate DATETIME, @HighDate DATETIME)RETURNS @Dates TABLE (SeqDate DATETIME)ASBEGINDECLARE @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)DECLARE @TotalRows int, @RowCnt intSET @TotalRows=1SET @RowCnt=1WHILE @RowCnt > 0 BEGIN INSERT @Dates (SeqDate) SELECT DATEADD(dd, @TotalRows, d.SeqDate) FROM @Dates d WHERE DATEADD(dd, @TotalRows, d.SeqDate) <= @HighDate SET @RowCnt=@@ROWCOUNT SET @TotalRows=@TotalRows+@RowCntENDRETURNEND

The difference is that I use a variable to count the number of rows (instead of using COUNT() in a derived table) which should be faster if the number of rows is really big. To benchmark, try running something like this:

DECLARE @t datetimeSET @t=GETDATE()SELECT COUNT(*) FROM dbo.fnSeqDates('20060101','23070131')PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'SET @t=GETDATE()SELECT COUNT(*) FROM dbo.MySeqDates('20060101','23070131')PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'

Of course, it's very unlikely that anyone would need such a long period (and for shorter periods the difference in performance is very small).

Razvan

RE: How many more Mondays until I retire?

Razvan Socol — Wed, 19 Jul 2006 08:13:00 GMT

In the first code sample, I suppose that this portion should return each number between 0 and 32, right ?

SELECT a.i + b.j + c.k 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) cORDER BY 1

Well... it doesn't. It generate some numbers between 0 and 32, but not all of them. The above query only generates 27 values. If you want to generate each number between 0 and 31, you can use this query:

SELECT a.i + b.j + c.k FROM (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) a, (SELECT 0 j UNION ALL SELECT 4 UNION ALL SELECT 8 UNION ALL SELECT 12) b, (SELECT 0 k UNION ALL SELECT 16) cORDER BY 1

Razvan

RE: How many more Mondays until I retire?

Jesper-244176 — Wed, 19 Jul 2006 07:08:00 GMT

Do you really need dates that old?

You could use

select datediff(d, '18000106', @dateTo)/7 - datediff(d, '18000107', @dateFrom)/7

instead. Note that

select datediff(d, '18000106', '1900-01-03')/7 - datediff(d, '18000107', '1899-12-29')/7

gives you one Monday.

My formula needs two reference dates, a Monday (e.g. 1900-01-01 or 1800-01-06) and the following Tuesday (e.g. 1900-01-02 or 1800-01-07). Both @dateFrom and @dateTo must be larger than these days for the formula to work. But OK, you found the weak point

RE: How many more Mondays until I retire?

SwePeso — Wed, 19 Jul 2006 06:35:00 GMT

Ok, ok ok

Still don't get proper result with date range 1899-12-29 and 1900-01-03.

Running

select datediff(d, '19000101', '1900-01-03')/7 - datediff(d, '19000102', '1899-12-29')/7

gives me 0 mondays. Shouldn't it report 1 monday for January 1, 1900?

RE: How many more Mondays until I retire?

Jesper-244176 — Wed, 19 Jul 2006 06:07:00 GMT

No, no, use the formula above:

select datediff(d, '19000101', '20060724')/7 - datediff(d, '19000102', '20060717')/7

returns 2, i.e 2 Mondays between the 17th and the 24th.

RE: How many more Mondays until I retire?

SwePeso — Wed, 19 Jul 2006 06:00:00 GMT

Yes, it works well if not more than one of the two dates already is a monday. Using this code

select (datediff(d, '20060213', '20060724') + 1)/7 - datediff(d, '20060213', '20060717')/7

reports 1 monday to me, where it should report 2 mondays.

But your query is very fast.

RE: How many more Mondays until I retire?

Jesper-244176 — Wed, 19 Jul 2006 04:47:00 GMT

Calculating the number of Mondays between two dates was the subject of the following thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=258968

My suggestion was the following:

select datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7

I still believe this is the fastest method to calculate the number of Mondays between two dates. On the other hand, it is not very flexible...

RE: How many more Mondays until I retire?

SwePeso — Wed, 19 Jul 2006 03:35:00 GMT

True, and it works too. But maybe not that versatile?

RE: How many more Mondays until I retire?

Ally MacKenzie — Wed, 19 Jul 2006 02:40:00 GMT

Nice article, alternatively...

SET DATEFIRST 1 -- force monday as the beginning of the week

DECLARE @bdy datetime, @RetiralAge smallint

SET @bdy='24-jul-1971'SET @RetiralAge=65

SELECT datediff (dd,getdate()+(8-datepart(dw,getdate())),dateadd(year,@RetiralAge,@bdy))/7 as "Mondays till you retire"

-Ally

How many more Mondays until I retire?

SwePeso — Thu, 22 Jun 2006 15:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

iterations	datediff	floor
1	0	0
10	0	0
100	0	0
1000	7	6
10000	68	75
100000	712	810