sql-server - query - sql remove time from datetime in where clause




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

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.

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.


Please try:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]

SQL2005: I recommend cast instead of dateadd. For example,

select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)

averaged around 10% faster on my dataset, than

select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)

(and casting into smalldatetime was faster still)


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.







date-conversion