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.

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:

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

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.

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
BE IT, India
http://JwalantSoneji.com