SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How many more Mondays until I retire?


How many more Mondays until I retire?

Author
Message
SwePeso
SwePeso
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3969 Visits: 3433
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


N 56°04'39.16"
E 12°55'05.25"
SwePeso
SwePeso
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3969 Visits: 3433

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

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

to

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.




N 56°04'39.16"
E 12°55'05.25"
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 Visits: 1803

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:

iterationdatedifffloatvarchar iterationdatedifffloatvarchar
1000 1000
10000 10000
100001 100002
10006712 10004713
100005770133 100007175137
1000006847721376 1000006497001355

'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?

S.


SwePeso
SwePeso
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3969 Visits: 3433

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.




N 56°04'39.16"
E 12°55'05.25"
Michael Meierruth
Michael Meierruth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1038 Visits: 2514

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 as
begin

declare @dwdest int
declare @padded_enddate datetime
declare @padded_workdays int
declare @dw int
declare @diff int

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

set @padded_enddate=@p_enddate
set @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)-1
if @dwdest=0 set @dwdest=7
while 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 padding
return @diff-@diff/7*2-@padded_workdays

end
go

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


Jwalant Natvarlal Soneji
Jwalant Natvarlal Soneji
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 77
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.

Jwalant Natvarlal Soneji
BE IT, India
http://JwalantSoneji.com


SwePeso
SwePeso
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3969 Visits: 3433
Thank you for your kind response.


N 56°04'39.16"
E 12°55'05.25"
SwePeso
SwePeso
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3969 Visits: 3433
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.

http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

You can try it out and comment it.


N 56°04'39.16"
E 12°55'05.25"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search