I have come across the need to combine date and time data from different columns on a number of occasions. Through the years I have always come up with a viable solution, but it always took me a few minutes longer than I thought it should. So, I decided to write a few of my favorite methods down, so maybe I will remember them, or at least have a reference of my own.
Add date column and time column together
I found a solution that works pretty well in a StackOverflow forum post
DECLARE @datet DATETIME; SET @datet = GETDATE(); SELECT @datet ,CAST(@datet AS DATE) ,CAST(@datet AS TIME); SELECT CAST(CAST(@datet AS DATE) AS DATETIME) ,CAST(CAST(@datet AS TIME) AS DATETIME) ,CAST(CAST(@datet AS DATE) AS DATETIME) + CAST(CAST(@datet AS TIME) AS DATETIME);
This method works well, and quite efficiently. The query above breaks down all the components into different columns to make it easier to understand. However I ran into a bit of a problem using it. Since SQL Server 2008 Microsoft has been saying to use DATETIME2 for all new work. Quoting an MSDN article:
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable.
So, I have been trying to use DATETIME2 instead of DATETIME for a while now. Interestingly enough, when I convert the above code to DATETIME2, it fails with the error:
Msg 8117, Level 16, State 1, Line 12
Operand data type datetime2 is invalid for add operator.
If I wanted my work to use the recommended standards I needed another way. Unfortunately DATEADD does not provide a direct way to add a full time to a date. Fortunately it does provide a way to add a time component to a date. So the first thing you need to do with the TIME value is to convert it to a component element. I generally choose seconds. If you need a higher or lower level of accuracy, you can change to suit your needs. Using only seconds I also keep my time data type set to TIME(0) my date time data type set to DATETIME2(0).
DECLARE @date DATE = GETDATE() ,@time TIME(0) = GETDATE(); SELECT @date ,@time; SELECT CAST(@date AS DATETIME2(0)) /*convert date to DATETIME(0) */ ,DATEDIFF(ss,0,@time) /*convert time to seconds */ ,DATEADD(ss, DATEDIFF(ss,0,@time),CAST(@date AS DATETIME2(0))); /*add seconds calculation value to DATETIME2(0) value */
First you convert your TIME(0) value to a seconds value by using DATEDIFF between the time value and zero. A time value of zero is functionally midnight. DATEDIFF between zero and any time value will give you the number of units between midnight and the time value. In my case it returns the number of seconds since midnight. Then I convert my date value to a DATETIME2(0) so it will allow a time component to be added. Finally I add the number of seconds to the Datetime value and the date and time values are combined.
I think it is also useful to point out that casting or converting any DATETIME value to either DATE or TIME is a very effective way to strip out the elements you need. Before the DATE or TIME data types came along it was more challenging.
I have also seen another approach where the DATE and TIME are converted to strings, concatenated, and then converted back to DATETIME or DATETIME2.
DECLARE @date DATE = GETDATE() ,@time TIME(0) = GETDATE(); SELECT @date ,@time; SELECT CAST(@date AS CHAR(10)) /* convert date to string */ ,CAST(@time AS CHAR(10)) /*convert time to string */ ,CAST(@date AS CHAR(10)) + ' ' + CAST(@time AS CHAR(10)) /* concatenate your strings */ ,CAST(CAST(@date AS CHAR(10)) + ' ' + CAST(@time AS CHAR(10)) AS DATETIME2(0)) /* convert string back to datetime */
Personally, when working with dates or times I prefer to keep the data types in the date time realm. I have always believed that date manipulation is all numeric and therefore a bit more CPU efficient than converting to a string and doing the work and then converting back to a date or datetime. However, for the sake of accuracy I decided to test this belief. I created a table with a date and a time column and loaded it with a 250,000 rows of data
CREATE TABLE [dbo].[DateTimeTest]( [TestDate] [date] NOT NULL, [TestTime] [time](0) NOT NULL ) ON [PRIMARY] GO DECLARE @Counter INT = 1; WHILE @Counter < 250000 BEGIN INSERT INTO DateTimeTest SELECT CAST(GETDATE() AS DATE) ,CAST(GETDATE() AS TIME(0)); SET @Counter = @Counter + 1; END;
Then I tested both the date math method and the string conversion method with SET STATISTICS TIME ON. I ran each test 4 times and took the average.
/* date method */ SELECT DATEADD(ss, DATEDIFF(ss,0,[TestTime]),CAST([TestDate] AS DATETIME2(0))) FROM [dbo].[DateTimeTest]; /* string method */ SELECT CAST(CAST([TestDate] AS CHAR(10)) + ' ' + CAST([TestTime] AS CHAR(10)) AS DATETIME2(0)) FROM [dbo].[DateTimeTest];
Keeping all the manipulation within the native date and time data types used 60% less CPU time than converting to a string and then back. So, I had my answer, using date math is indeed more efficient than converting to strings and then back to dates.
I hope you find this information useful. Thanks for reading.