Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

How many more Mondays until I retire? Expand / Collapse
Author
Message
Posted Thursday, July 20, 2006 3:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:14 AM
Points: 2,393, Visits: 3,395
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"
Post #295812
Posted Thursday, July 20, 2006 3:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:14 AM
Points: 2,393, Visits: 3,395

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"
Post #295818
Posted Thursday, July 20, 2006 6:53 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 605, Visits: 1,691

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.

Post #296118
Posted Friday, July 21, 2006 2:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:14 AM
Points: 2,393, Visits: 3,395

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"
Post #296162
Posted Friday, July 21, 2006 10:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 531, Visits: 2,074

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.

 

 

Post #296392
Posted Monday, December 10, 2007 11:19 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 7:59 AM
Points: 21, Visits: 74
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 .
Thanks.


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

Post #431636
Posted Tuesday, December 11, 2007 3:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:14 AM
Points: 2,393, Visits: 3,395
Thank you for your kind response.



N 56°04'39.16"
E 12°55'05.25"
Post #431678
Posted Wednesday, June 17, 2009 3:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:14 AM
Points: 2,393, Visits: 3,395
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"
Post #737042
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse