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.
- It has to be as fast as possible (the less casting, the better).
The final result has to be a
datetimetype, 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.
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)
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.