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.