Alex Rivera | Logout

Best approach to remove time part of datetime in SQL Server

Asked 2009-07-24T12:49:54.420
612

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?

Edit
Report

1 Answer

2
CAST(round(cast(getdate()as real),0,1) AS datetime)

This method does not use string function. Date is basically a real datatype with digits before decimal are fraction of a day.

this I guess will be faster than a lot.

answered 2011-11-18T12:57:26.040

Your Answer