How to remove the time portion of a datetime value (SQL Server)?

user_434 0
Viewed 63466

Here's what I use:

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

I'm thinking there may be a better and more elegant way.

Requirements:

  • It has to be as fast as possible (the less casting, the better).
  • The final result has to be a datetime type, not a string.
date-conversion datetime sql-server
6 Answers

SQL Server 2008 has a new date data type and this simplifies this problem to:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
user_950

Itzik Ben-Gan in DATETIME Calculations, Part 1 (SQL Server Magazine, February 2007) shows three methods of performing such a conversion (slowest to fastest; the difference between second and third method is small):

SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

SELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime)

Your technique (casting to float) is suggested by a reader in the April issue of the magazine. According to him, it has performance comparable to that of second technique presented above.

user_950

Your CAST-FLOOR-CAST already seems to be the optimum way, at least on MS SQL Server 2005.

Some other solutions I've seen have a string-conversion, like Select Convert(varchar(11), getdate(),101) in them, which is slower by a factor of 10.

user_910
Related