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.
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.
Don't convert datetime to varchar, unless you need result as string for reporting purposes.
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.
declare @loop int, @d datetimedeclare @lMax intset @lMax = 3000declare @dStart datetime, @dEnd datetime
--various methods to strip off the time portion off a date
set nocount onset @loop = 1set @dStart = getdate()--uses convert while @loop <@lMaxbegin set @d= CONVERT(datetime, CONVERT(varchar, getdate(), 101)) set @loop = @loop + 1endselect datediff(ms, @dStart,getdate())
set @loop = 1set @dStart = getdate()--uses castwhile @loop <@lMaxbegin set @d = cast(floor(cast(getdate() as float)) as datetime) set @loop = @loop + 1endselect datediff(ms, @dStart,getdate())
set @loop = 1set @dStart = getdate()--uses datediff, dateaddwhile @loop <@lMaxbegin set @d= dateadd(d, 0, datediff(d, 0, getdate())) set @loop = @loop + 1endselect datediff(ms, @dStart,getdate())
set nocount off