Truncating Date
-
What is the best/most efficient way to truncate DateTime to it's Date value only?
I have a stored procedure that is receiving DateTime and I only want to insert Date part of it (truncate time to 0:00:00)
I don't see any function for this and the only solution I could come up with is to string concatanation with year,month,day function and cast to date back, but that seems way too unefficient.
Thanks.
-
I found a solution on google:
SELECT DATEADD(dd, DATEDIFF(dd,0,@x), 0)
but that seems odd to do datediff and dateadd to get the result.
I wish there was a built in function for that.
-
The solution you have is reasonable, but there are many variations. I usually cast the date to a float, truncate it, and cast back to a date.
SELECT CAST(FLOOR(CAST(@x AS FLOAT)) AS DATETIME)
You would probably have to execute this conversion many times to see a performance difference, if any.
PS You can't use CAST(CAST(@x AS INT) AS DATETIME) because it will round PM times up to the next date.
-
use convert and apply a style to get only the date...
select convert(varchar, getdate(), 101)
there're any # of formats that you can apply - check BOL for the one that fits your need best!
**ASCII stupid question, get a stupid ANSI !!!** -
I would think this would be slower than DateAdd solution as this converts to varchar and back.
-
Absolutely!
Don't convert datetime to varchar, unless you need result as string for reporting purposes.
_____________
Code for TallyGenerator -
Converting from datetime to varchar and back would be an awful thing to do for looking at a million rows, but performed once per call to modify a stored procedure parameter will not measurably affect performance (unless you are calling the procedure a million times, and then you have bigger problems
-
Just out of curiosity, WHY do you not want to store the time? I mean, storing a 0 time doesn't save you any space. It's easy enough to use the DATEPART functions when comparing the values, to ignore the time portion. I might see some advantage to indexing, but that's about it.
-
This comes up regularly. Ive tested most scenarios in the past and datediff/dateadd always comes out the fastest - often by a good margin. It also has the advantage of being locale-independent (doesnt matter whether your dates are ddmmyyy or mmddyyyy or whatever).
-
Here a script you can use to test the 3 methods.
There is a measureable difference between the convert to varchar and the other 2 options.
Only when you increase the number of iterations > 30,000 do you start to see the speed improvement of the last one
(dateadd(datediff)) but as others have said for a small number of conversions any will work - as long as you take into account the locale specific needs for the varchar option.
Kevin
*****************************
declare @loop int, @d datetime
declare @lMax int
set @lMax = 3000
declare @dStart datetime, @dEnd datetime
--various methods to strip off the time portion off a date
set nocount on
set @loop = 1
set @dStart = getdate()
--uses convert
while @loop <@lMax
begin
set @d= CONVERT(datetime, CONVERT(varchar, getdate(), 101))
set @loop = @loop + 1
end
select datediff(ms, @dStart,getdate())
set @loop = 1
set @dStart = getdate()
--uses cast
while @loop <@lMax
begin
set @d = cast(floor(cast(getdate() as float)) as datetime)
set @loop = @loop + 1
end
select datediff(ms, @dStart,getdate())
set @loop = 1
set @dStart = getdate()
--uses datediff, dateadd
while @loop <@lMax
begin
set @d= dateadd(d, 0, datediff(d, 0, getdate()))
set @loop = @loop + 1
end
select datediff(ms, @dStart,getdate())
set nocount off
-
I did some testing on our oldest, slowest server and found the datediff/dateadd method squeaks past the floor(cast as float) method by 1 microsecond (3 microsec vs 4 microsec), while convert(char(10), @date, 120) is 5 times slower at 20 microsec.
In an actual query, I doubt you would actually see a 1 second difference on a million-row query because the server probably has CPU time to spare while it waits for pages to be read from disk. I would pick any numeric method based on which one I felt was easier to read and maintain. For me that is the floor(cast as float), but I'm just used to doing it that way.
I think you would notice a difference using any character method, simply due to the effort involved in allocating and garbage collecting all those strings. Anyone who programmed back in the old days in a real language with no sissy objects, and had to carry strings on their backs uphill 10 miles both ways
, would never stoop to using the character conversion because it was easier. -
If you use SELECT Convert(varchar(8), GetDate(), 112), which returns the date in YYYYMMDD format, you avoid any locality issues.
However, in my tests, SELECT CAST(FLOOR(CAST(@x AS FLOAT)) AS DATETIME) runs almost 3 times faster than the convert method (which is what i've used in the past - I've only recently started using the "floor" method).
-
I think he is doing it for BI and performance reason.
What if you only WANT to store a date? For example if you want to store the different holidays... They do not occur on a specific time.
DATEPART can be good in some cases. But not to use in a WHERE DATEPART( ... , myColumn ) = <something>. That can not be indexed (this is not some disadvantage, this is a HUGE disadvantage) and results in (at best) a total index scan.
The solution Darren found is the most efficient one. Bad queries on small data sets will probably not make you loose your job. But when your tables starts growing in the hundreds of millions of rows you will regret the day you were born
Regards,
Hans
-
Add this to the script for comparing the perfomance of method #4:
convert(datetime,convert(int,GetDate()-.5))
set @loop = 1
set @dStart = getdate()
--uses int conversion
while @loop <@lMax
begin
set @d= convert(datetime,convert(int,GetDate()-.5))
set @loop = @loop + 1
end
select datediff(ms, @dStart,getdate())
jg
-
I used almost the identical script Kevin posted for comparing the methods, but you have to put one empty loop in to find the baseline time for the incrementing & looping statements. For 1 million iterations I got about 7.5 sec for datediff/dateadd and 8.5 sec for floor(cast) showing about a 10% difference, but the empty loop took 4.5 sec so the real time difference is 3 sec vs 4 sec or 25%.
set @loop = 1
set @dStart = getdate()
--baseline
while @loop <@lMax
begin
set @loop = @loop + 1
end
select datediff(ms, @dStart,getdate())
So datediff/dateadd is slightly faster, but it is hard to imagine a real-world query where this difference would be noticable. I still say choose the method that looks more readable/maintainable to you. On the other hand, I'm not an expert on SQL Server internals but I assume 1 million CONVERT(char,...) functions will cause 1 million heap allocations and deallocations to create/destroy the strings, and this will be noticable.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply