The Fastest Way to Combine DATE and TIME Data Types to a DATETIME
Recently while working with a table where there were two columns, one a DATE datatype and a second TIME datatype, I found that those needed to be combined into a single DATETIME result column. I won’t go into why the DATE and TIME were separated in this case, but suffice it to say that it seemed like a good idea at the time.
Just like most date arithmetic in T-SQL, there are many ways to do this. Let’s look at the first thought that came to my mind.
DECLARE @MyDate DATE = '2015-08-27' ,@MyTime TIME = '15:33:21.057'; SELECT MyDateTime1=CAST(@MyDate AS DATETIME) + CAST(@MyTime AS DATETIME);
This works because in T-SQL it is possible to add (or subtract) two DATETIME values, and you’ll find that it produces exactly the desired result, which is: 2015-08-27 15:33:21.057.
Knowing from past experience that the first query form that jumps into your brain, isn’t always the fastest one from a performance perspective, I decided to explore some other alternatives.
Here’s another way that will work:
SELECT MyDateTime2=DATEADD(millisecond ,DATEDIFF(millisecond, 0, @MyTime) ,CAST(@MyDate AS DATETIME));
If you are unfamiliar with the DATEADD and DATEDIFF functions, you can use those links to get to the Microsoft Books-on-Line reference, or you can see some examples in one of my prior blogs: Manipulating Dates and Times in T-SQL.
Here’s yet another:
SELECT MyDateTime3=DATEADD(day ,DATEDIFF(day, 0, @MyDate) ,CAST(@MyTime AS DATETIME));
Finally, we’ll get a little creative and use a VARCHAR intermediate result to come up with this:
SELECT MyDateTime4=CAST( CAST(@MyDate AS VARCHAR(10)) + ' ' + CAST(@MyTime AS VARCHAR(12)) AS DATETIME);
So now we have some choices, but which do we use?
The first one is the shortest in terms of keystrokes, and arguably the easiest one to understand, although some might argue that the third one is easier to understand.
From my perspective though, and I’ve said this before and I’m likely to say it again, performance always counts. Even when this tiny fragment of code is part of a much larger T-SQL query, getting the fastest speed out of that complex query means getting the fastest speed out of each of its tiniest parts.
So naturally we’ll turn to one of our favorite performance tools, the One Million Row Test Harness. It is so easy to create one of these for this case it is almost embarrassing, the embarrassing part of course being how few people might consider doing so. Here it is.
WITH Tally (n) AS ( SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b ) SELECT MyDate = DATEADD(day, 1+ABS(CHECKSUM(NEWID()))%10000, CAST('1900-01-01' AS DATE)) ,MyTime = DATEADD(millisecond, 1+ABS(CHECKSUM(NEWID()))%86400000, CAST('00:00' AS TIME)) INTO #Temp FROM Tally; DECLARE @MyDateTime DATETIME; SET STATISTICS TIME ON; SELECT @MyDateTime=CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME) FROM #Temp; SET STATISTICS TIME OFF; SET STATISTICS TIME ON; SELECT @MyDateTime=DATEADD(millisecond ,DATEDIFF(millisecond, 0, MyTime) ,CAST(MyDate AS DATETIME)) FROM #Temp; SET STATISTICS TIME OFF; SET STATISTICS TIME ON; SELECT @MyDateTime=DATEADD(day ,DATEDIFF(day, 0, MyDate) ,CAST(MyTime AS DATETIME)) FROM #Temp; SET STATISTICS TIME OFF; SET STATISTICS TIME ON; SELECT @MyDateTime=CAST( CAST(MyDate AS VARCHAR(10)) + ' ' + CAST(MyTime AS VARCHAR(12)) AS DATETIME) FROM #Temp; SET STATISTICS TIME OFF; GO DROP TABLE #Temp;
Short and sweet! You gotta love that in a performance test harness.
In each case we’ve shunted the results to a local variable to avoid inevitable delays rendering our one million rows of test data to the SSMS Results pane, which might cause some variability in the timing results.
We chose the specific in-line Tally Table we’re using, because it is probably one of the fastest to type in, due to its short length in terms of characters. Needless to say, any one million row tally table would suffice.
The SELECT INTO construct also makes it pretty easy to check that we’re actually creating DATE and TIME datatypes into our #Temp table, simply by commenting out the INTO part of the query and rendering the results to the screen.
By now I’ve done enough talking, so I’ll cut to the chase. That is, which of our four methods of combining a DATE and a TIME into a DATETIME ends up being the fastest?
(1000000 row(s) affected) SQL Server Execution Times: CPU time = 171 ms, elapsed time = 174 ms. SQL Server Execution Times: CPU time = 234 ms, elapsed time = 233 ms. SQL Server Execution Times: CPU time = 234 ms, elapsed time = 233 ms. SQL Server Execution Times: CPU time = 1123 ms, elapsed time = 1113 ms.
There we have it! As it turns out, my first thought seems to be outperforming the other methods quite handily, especially that nasty one that uses the VARCHAR intermediate. That won’t always be the case, so it is important that for things like this you get creative and see what your SQL-brain can come up with.
If anyone has a better method of doing this combination/conversion, please post it and your results using my test harness to the comments section.
We’ve demonstrated a couple of things here which I think are quite important:
- How easy it is to create a one million row test harness to compare different queries to see which is the fastest.
- Make it Work, Make it Fast and Make it Pretty – OK, so I forgot to do that last part! 😉 I’m saving that for the production query.
Hope to hear from you soon!
Follow me on Twitter: @DwainCSQL
© Copyright Dwain Camps 28 Aug 2015. All rights reserved.
14 thoughts on “The Fastest Way to Combine DATE and TIME Data Types to a DATETIME”
August 29, 2015 at 8:33 pm
It is nice to have the performance numbers on this. But more than that, this is how we would do it in ANSI/ISO Standard SQL. It says that MS is moving to get aligned with standards and get rif of the old Sybase legacy (aka “family curse”) code.
August 31, 2015 at 10:00 am
Your script is using @MyDate and @MyTime at various points – you need to remove the ampersand
August 31, 2015 at 10:56 am
You are right. Sorry about that! I believe I have fixed that now. Thanks for the catch.
September 8, 2015 at 2:48 pm
DATEADD returns a smalldatetime, so your #temp table has two smalldatetime fields rather than a date and a time field.
When you return the querys into a result set rather than into @MyDateTime, I got 391ms, 468ms, 407ms and 1250ms vs 234ms, 281ms, 250ms and 891ms using SQL 2016
September 9, 2015 at 2:33 pm
@Jame Smith: check BOL. “The return data type is the data type of the date argument, except for string literals. The return data type for a string literal is datetime.”
September 8, 2015 at 7:33 pm
Your solution is not accurate if TIME is defined with fractional second precision of 7 – which is the default. This solution gives an accurate result
DECLARE @d DATE = SYSDATETIME();
DECLARE @t TIME = SYSDATETIME();
SELECT @d, @t, DATEADD(DAY, DATEDIFF(DAY, CAST(‘1900-1-1’ AS DATE), @d), CAST(@t AS DATETIME2))
September 9, 2015 at 4:22 pm
Carsten – You are correct if the target result is SQL DATETIME2. In my case, since the target data type was DATETIME, my solutions were accurate enough.
September 9, 2015 at 2:49 pm
September 9, 2015 at 4:19 pm
Paulo – I got this timing from your suggestion using the test harness in the article.
September 9, 2015 at 6:25 pm
Odd! Isn’t it? Have you tried for the other datetime formats?
September 10, 2015 at 7:04 am
Actually I don’t consider the result that I got particularly odd at all. When I see the methodology you used my first thought is “gee whiz, that look’s like a lot of work to perform to get the job done” (although its correctness is not in dispute).
The answer to your question about exploring this with other date/time data types is no. What I needed for my production query is pretty well stated in the subject of the blog. As it is a blog, it is usually a good idea to keep it short and to the point.
That doesn’t mean I won’t explore it another day of course.
September 11, 2015 at 1:04 am
I could imagine that the increase here comes from invocation of the CLR constructor for a DateTime object. AFAIK DATETIMEFROMPARTS is not a native SQL Server function but some sort of wrapper for the DateTime constructor. I have been using the DATEADD/DATEDIFF approach for years already, unfortunately this is way beyond the horizon for some .Net developers. 😉
September 21, 2015 at 12:33 am
You are testing the speed of the conversion functions and I get that. Yet you start out by telling us that you have two columns: one DATE and one TIME.
So how do these conversions hold up if either or both columns are NULL?
September 21, 2015 at 7:30 am
The truth is I didn’t test with NULLs. Mainly because in my case the data doesn’t contain any. I’d welcome hearing back from you on what you find though!