Most efficient way in SQL Server to get date from date+time?
I must admit I hadn't seen the floor-float conversion shown by Matt before. I had to test this out.
I tested a pure select (which will return Date and Time, and is not what we want), the reigning solution here (floor-float), a common 'naive' one mentioned here (stringconvert) and the one mentioned here that I was using (as I thought it was the fastest).
I tested the queries on a test-server MS SQL Server 2005 running on a Win 2003 SP2 Server with a Xeon 3GHz CPU running on max memory (32 bit, so that's about 3.5 Gb). It's night where I am so the machine is idling along at almost no load. I've got it all to myself.
Here's the log from my test-run selecting from a large table containing timestamps varying down to the millisecond level. This particular dataset includes dates ranging over 2.5 years. The table itself has over 130 million rows, so that's why I restrict to the top million.
SELECT TOP 1000000 CRETS FROM tblMeasureLogv2
SELECT TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME) FROM tblMeasureLogv2
SELECT TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120) FROM tblMeasureLogv2
SELECT TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0) FROM tblMeasureLogv2
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 422 ms, elapsed time = 33803 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 625 ms, elapsed time = 33545 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 1953 ms, elapsed time = 33843 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 531 ms, elapsed time = 33440 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
What are we seeing here?
Let's focus on the CPU time (we're looking at conversion), and we can see that we have the following numbers:
Pure-Select: 422
Floor-cast: 625
String-conv: 1953
DateAdd: 531
From this it looks to me like the DateAdd (at least in this particular case) is slightly faster than the floor-cast method.
Before you go there, I ran this test several times, with the order of the queries changed, same-ish results.
Is this something strange on my server, or what?
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
DateDiff(Day, 0, GetDate()) is the same as DateDiff(Day, '1900-01-01', GetDate())
Since DateDiff returns an integer, you will get the number of days that have elapsed since Jan 1, 1900. You then add that integer number of days to Jan 1, 1900. The net effect is removing the time component.
I should also mention that this method works for any date/time part (like year, quarter, month, day, hour, minute, and second).
Select DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)
Select DateAdd(Quarter, DateDiff(Quarter, 0, GetDate()), 0)
Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
Select DateAdd(Hour, DateDiff(Hour, 0, GetDate()), 0)
Select DateAdd(Second, DateDiff(Second, '20000101', GetDate()), '20000101')
The last one, for seconds, requires special handling. If you use Jan 1, 1900 you will get an error.
Difference of two datetime columns caused overflow at runtime.
You can circumvent this error by using a different reference date (like Jan 1, 2000).
select cast(floor(cast(@datetime as float)) as datetime)
Works because casting a datetime to float gives the number of days (including fractions of a day) since Jan 1, 1900. Flooring it removes the fractional days and leaves the number of whole days, which can then be cast back to a datetime.