Best approach to remove time part of datetime in S

Question

Which method provides the best performance when removing the time portion from a datetime field in SQL Server?

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

or

b) select cast(convert(char(11), getdate(), 113) as datetime)

The second method does send a few more bytes either way but that might not be as important as the speed of the conversion.

Both also appear to be very fast, but there might be a difference in speed when dealing with hundreds-of-thousands or more rows?

Also, is it possible that there are even better methods to get rid of the time portion of a datetime in SQL?

Answer 1

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

Answer 2

Just in case anyone is looking in here for a Sybase version since several of the versions above didn't work

CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME)

• Tested in I SQL v11 running on Adaptive Server 15.7

Answer 3

In SQL Server 2008, there is a DATE datetype (also a TIME datatype).

CAST(GetDate() as DATE)

or

declare @Dt as DATE = GetDate()

Answer 4

Already answered but ill throw this out there too... this suposedly also preforms well but it works by throwing away the decimal (which stores time) from the float and returning only whole part (which is date)

 CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)

second time I found this solution... i grabbed this code off

Answer 5

I think you mean cast(floor(cast(getdate()as float))as datetime)

real is only 32-bits, and could lose some information

This is fastest cast(cast(getdate()+x-0.5 as int)as datetime)

...though only about 10% faster(about 0.49 microseconds CPU vs. 0.58)

This was recommended, and takes the same time in my test just now: DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

In SQL 2008, the SQL CLR function is about 5 times faster than using a SQL function would be, at 1.35 microseconds versus 6.5 microsections, indicating much lower function-call overhead for a SQL CLR function versus a simple SQL UDF.

In SQL 2005, the SQL CLR function is 16 times faster, per my testing, versus this slow function:

create function dateonly (  @dt datetime )
returns datetime
as
begin
return cast(floor(cast(@dt as float))as int)
end