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.

Absolutely!

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.

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