Most efficient way in SQL Server to get date from

Question

In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'?

Duplicated here.

Answer 1

CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME) performs the best by far. you can see the proof & tests when getting the date without time in sql server

Answer 2

What About SELECT CAST(CASt(GETDATE() AS int) AS DATETIME)??

Answer 3

select cast(getdate()as varchar(11))as datetime

Answer 4

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?

Answer 5

Three methods described in the link below. I haven't performance tested them to determine which is quickest.

http://www.blackwasp.co.uk/SQLDateFromDateTime.aspx

Answer 6

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.