Most efficient way in SQL Server to get date from date+time?

by

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?

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. How to calculate age (in years) based on Date of Birth and getDate()
  3. CROSS JOIN vs INNER JOIN in SQL
  4. Referring to a Column Alias in a WHERE Clause
  5. Convert Month Number to Month Name Function in SQL
  6. Function vs. Stored Procedure in SQL Server
  7. Multiple INSERT statements vs. single INSERT with multiple VALUES
  8. Limit on the WHERE col IN (…) condition
  9. How do you list the primary key of a SQL Server table?
  10. Generate Dates between date ranges
  11. Select group of rows that match all items in a list
  12. Calculating distance between two points (Latitude, Longitude)
  13. SQL Server check case-sensitivity?
  14. How to print VARCHAR(MAX) using Print Statement?
  15. How to update Identity Column in SQL Server?
  16. Concatenate values based on ID
  17. Query error with ambiguous column name in SQL
  18. Why (and how) to split column using master..spt_values?
  19. What does =* mean?
  20. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  21. SQL Select Upcoming Birthdays
  22. How can I include null values in a MIN or MAX?
  23. Find the real column name of an alias used in a view?
  24. How to get the identity of an inserted row?
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. SQL Server FOR XML Path make repeating nodes
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Group consecutive rows of same value using time spans
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment