Removing the time from a datetime is a task that comes up again and again and it sometimes seems like everyone’s got their own favorite way of doing it. I though I’d take a run through some of the ways I’ve seen and see how the compare in terms of performance.
The methods that I’ll be comparing are:
- Cast to varchar and back to datetime
- Cast to float, floor and cast back to datetime
- Use dateadd and datediff to get rid of the time
- Cast to date and back to datetime (SQL 2008 specific)
I ran each one 4 times and averaged the results
CREATE TABLE #DateTest ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, DateValue DATETIME ) INSERT INTO #DateTest (DateValue) SELECT TOP 1000000 DATEADD(mi,RAND(s1.number*5000 + s2.number)*5000,'2000/01/01' ) FROM master..spt_values s1 CROSS JOIN master..spt_values s2 WHERE s1.[name] IS NULL AND s2.NAME IS null GO SET NOCOUNT ON GO SET STATISTICS TIME ON GO SELECT dateadd(dd, datediff(dd,0, DateValue),0) FROM #DateTest GO 4 SELECT convert(datetime,convert(varchar(10),DateValue,101)) FROM #DateTest GO 4 SELECT CAST(FLOOR(CAST(DateValue AS FLOAT)) AS DATETIME) FROM #DateTest GO 4 SELECT CAST(CAST(DateValue AS DATE) AS DATETIME) FROM #DateTest GO 4 DROP TABLE #DateTest
And so, the winner of the date challenge:
- Dateadd and datediff – CPU time 348ms, execution time 8092 ms
- Cast to float and floor – CPU time 395ms, execution time 8270ms
- Cast to varchar – CPU time 1450ms, execution time 9280ms
- Cast to Date – CPU time 3063ms, execution time 7905ms
I was not expecing the cast to Date to take so much processing time. I think I’m going to take a look at the new date types in more detail, see what they perform like.
Same duration, slightly less CPU.
However I think it will not work on SQL Server 2008.
SELECT CAST(CAST(DateValue – 0.50000004 AS INT) AS DATETIME)
FROM #DateTest
GO 4
It works in 2008. Won’t work with the datetime2 data type though. But then neither does the method that casts to float.
On the same test box as the others, that one came in at:
CPU time 356ms, execution time 7994 ms
Interesting in that my results turned out differently (same table and data as you) as follows;
1. Cast to float to floor
2. Cast to Date
3. DateAdd and Datediff
4. Cast to varchar (sadly this performed as poorly as yours)
So, with that being said, I don’t believe environment should make that much of a difference (dual core laptop with a bunch of memory running 2008). Curious if you have any thoughts why the disparity.
What kind of numbers did you get?
I can see the dateadd and the cast to float coming out in a different order. They were close in both CPU and execution time.
One thing I did notice (and should have mentioned) is that I forced a MAXDOP 1 on all of the queries when I ran them, as some were running parallel and some were not. I noticed that the parallel plans were significantly slower than the serial plans. Try that, see if it changes things.
To time the date operations alone, you should use a SET command that truncates a date variable instead of a SELECT to avoid any influence from I/O or cache. Put it in a loop that executes a million times, then subtract the time for an empty loop.
I’ve always found the dateadd/datediff method to be fastest. It only requires moving a 32-bit integer. Casting to a float is a little slower as it requires floating point arithmetic. Converting to character is terrible as it requires allocating and garbage collecting millions of string objects.
I would expect select CONVERT(datetime,datediff(dd,0,datevalue)) to be slightly faster than DateAdd DateDiff, as it removes one calculation
Never mind that, it didn’t work properly. Thus the importance of testing before speaking.