Following up on my previous post about trimming time from datetime, I was spurred to demonstrate more clearly the performance characteristics of various methods without involving data access. In the original post, I quickly compared seven different methods of converting a datetime value to a date independently, showed that the differences were negligible, then moved straight into analyzing the use of those methods in actual queries that return data.
In this post I wanted to show several different ways to trim time from datetime (18 different ways in fact!), without introducing any actual data, to see if we could proclaim a "fastest" way to perform this task.
Here are the 18 methods I would be testing, some taken from the blog post Madhivanan pointed out after my previous post:
DECLARE @d DATETIME, @ds DATETIME = SYSDATETIME();
I created a loop where I would run each conversion 1,000,000 times, and then repeat the process for all 18 conversion methods 10 times. This would provide metrics for 10,000,000 conversions for each method, eliminating any significant statistical skew.
CREATE TABLE #s(j INT, ms INT); GO SET NOCOUNT ON; GO DECLARE @j INT = 1, @x INT, @i INT = 1000000; DECLARE @t DATETIME2, @d DATETIME, @ds DATETIME = SYSDATETIME(); WHILE @j <= 18 BEGIN SELECT @x = 1, @t = SYSDATETIME(); WHILE @x <= @i BEGIN IF @j = 1 SET @d = DATEDIFF(DAY, 0, @ds); IF @j = 2 SET @d = CAST(@ds AS INT); IF @j = 3 SET @d = CAST(CONVERT(CHAR(8), @ds, 112) AS DATETIME); IF @j = 4 SET @d = DATEADD(DAY, DATEDIFF(DAY, 0, @ds), 0); IF @j = 5 SET @d = CAST(CAST(SUBSTRING(CAST(@ds AS BINARY(8)), 1, 4) AS BINARY(8)) AS DATETIME); IF @j = 6 SET @d = CONVERT(CHAR(8), @ds, 112); IF @J = 7 SET @d = CAST(CAST(@ds AS VARCHAR(11)) AS DATETIME); IF @J = 8 SET @d = @ds - CONVERT(CHAR(10), @ds, 108); IF @J = 9 SET @d = @ds - CAST(CAST(@ds AS TIME) AS DATETIME); IF @J = 10 SET @d = CAST(FLOOR(CAST(@ds AS FLOAT)) AS DATETIME); IF @J = 11 SET @d = CAST(CAST(CAST(CAST(@ds AS BINARY(8)) AS BINARY(4)) AS BINARY(8)) AS DATETIME); IF @J = 12 SET @d = @ds - CAST(@ds AS BINARY(4)); IF @J = 13 SET @d = DATEADD(DAY, CONVERT(INT, @ds - 0.5), 0); IF @J = 14 SET @d = CONVERT(DATETIME, FORMAT(@ds, N'yyyy-MM-dd')); IF @J = 15 SET @d = CONVERT(DATETIME,CONVERT(INT,CONVERT(FLOAT,@ds))); IF @J = 16 SET @d = CAST(CAST(CAST(CAST(@ds AS BINARY(8)) AS BIGINT) & 0XFFFFFFFF00000000 AS BINARY(8)) AS DATETIME); IF @J = 17 SET @d = CONVERT(DATE, @ds); IF @j = 18 SET @d = CAST(@ds AS DATE); SET @x += 1; END INSERT #s SELECT @j, DATEDIFF(MILLISECOND, @t, SYSDATETIME()); SET @j += 1; END GO 10 SELECT j, method = CASE ... END, MIN(ms), MAX(ms), AVG(ms) FROM #s GROUP BY j ORDER BY j;
I ran this on a Windows 8 VM, with 8 GB RAM and 4 vCPUs, running SQL Server 2012 (11.0.2376). Here are tabular results, sorted by average duration, fastest first:
And here is a graphical representation of the average duration:
If we removed the outlier (which uses SQL Server 2012's new
FORMAT function, an obvious dog for this purpose), we'd have a real hard time picking a true loser here. Of the remaining 17 methods, to perform this conversion a million times, the slowest method is only three seconds slower than the fastest method, on average. The evidence still supports my earlier assertion that using
CAST / CONVERT natively is about as efficient as you can get, but the improvement over the other approaches is only marginal, and it wasn't even the winner in every single run.
8 thoughts on “Trimming time from datetime – a follow-up”
A lot of those won't work with a DATETIME2 datatype…
Yes, understood. I think they will all work with an explicit convert (if the destination is datetime2 but the source is not), but the point is that it the performance doesn't matter all that much, so use what works.
Good Post Aaron. The link that points to my blog post is my first post that shows 6 methods. My follow up post on comparing many methods that include methods suggested by readers has 12 methods. You may need to point to this post too Different ways to remove TIME part from DATETIME values – Faster methods (http://beyondrelational.com/modules/2/blogs/70/Posts/17608/different-ways-to-remove-time-part-from-datetime-values-faster-methods.aspx)
Methods 17/18 will still keep index SARGability if you're doing this on a column and there is an index on it. So there's that advantage over the others.
Thanks Phil, I did touch on that in my previous post. Here I was trying to demonstrate that in terms of raw speed (no data or indexes involved) most of the techniques are roughly a wash.
Just for documentation 3 years later: Don't forget method 14 breaks completely in non-english language settings. MS assumes europeans actually use that one datefmt that no culture in this whole planet ever used (ydm).
Some don't work correctly to begin with. For example…
DECLARE @d DATETIME, @ds DATETIME = '20171230 23:59:59.997';
–===== Method #2 (Incorrect Return)
SELECT @d = CAST(@ds AS INT);
SELECT @d, @ds;
–===== Method #13 (Incorrect Return)
SELECT @d = DATEADD(DAY, CONVERT(INT, @ds – 0.5), 0);
SELECT @d, @ds;
And, sorry, the forum software ate one of the dashes on each of the comments.
Comments are closed.