How many more Mondays until I retire?
-
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?S. -
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
-
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)
N 56°04'39.16"
E 12°55'05.25" -
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
-
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
)
AS
BEGIN
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
RETURN
END
GO
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 Numbers
FROM
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
-
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" -
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" -
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?
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.
N 56°04'39.16"
E 12°55'05.25" -
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.
-
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.
-
Thank you for your kind response.
N 56°04'39.16"
E 12°55'05.25" -
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"
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply