How to Combine Time and Date in SQL SERVER? – Interview Question of the Week #285

July 12, 2020

Question: How to Combine Time and Date in SQL SERVER?

Answer: A very simple and popular question. Let us see how we can combine and date and time in SQL Server.

How to Combine Time and Date in SQL SERVER? - Interview Question of the Week #285 CombineTimeDate-800x201

Method 1: Using + Combine Time Date

Let us run the following command:

DECLARE @dt DATE = '2020-07-12'
DECLARE @tm TIME = '07:01:01.000'
SELECT CAST(@dt AS DATETIME) + CAST(@tm AS DATETIME) AS [CombineDateTime]

Method 2: Using DATEADD

Let us run the following command:

DECLARE @dt DATE = '2020-07-12'
DECLARE @tm TIME = '07:01:01.000'
SELECT DATEADD(DAY,DATEDIFF(DAY, 0, @dt),CAST(@tm AS DATETIME)) AS [CombineDateTime]

There is also one more method I often see people using in the real world is the function CONCAT. I would love to see your answer here in the comment section. Please leave a comment with your answer – How we can use the CONCAT functions to combine time and date in SQL Server?

Here are the blog posts in the series which you can read in order to learn more about this topic:

If you are CTO, CIO, or CXO who is responsible to move your organizations to the cloud you should read the blog posts listed above. If your organizations are not on the cloud, you should consider introspecting in your technology stack and applications as I am very confident that there will be set of application which will be more suitable for cloud and there will be set of tools which are best kept at on-premises.

Here is another interview like a puzzle which may interest you: SQL SERVER – Computed Column and Compute Scalar Operators. My client asked me a very interesting question about the computed column. The question is as follows: When any computed column is retrieved from a table, why are there two scalar operations in the execution plan?

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DateTime, SQL Function, SQL Scripts, SQL Server

What are Different Layers of Open Source Cloud? – Interview Question of the Week #284

How to Generate Script for SQL Server 2005 from SQL Server 2019? – Interview Question of the Week #285

4 Comments. Leave new

suresh babu
July 17, 2020 11:18 am
DECLARE @dt DATE = ‘2020-07-12′
DECLARE @tm TIME = ’07:01:01.000′
SELECT concat(@dt,’ ‘, @tm) AS [CombineDateTime]
Reply
Barak G.
September 16, 2020 3:12 am
DECLARE @dt DATE = ‘2020-07-12′
DECLARE @tm TIME = ’07:01:01.000’
SELECT CAST(concat(@dt, ‘ ‘, @tm) as datetime2(0) /* my preference */) CombineDateTime
Reply
Jeff Moden
March 30, 2023 9:43 pm
Ah… be careful now. A TIME column defaults to TIME(7). Any reduction in precision, like converting to DATETIME, will cause rounding. While you might be able to put up with 1 or 2 milliseconds of rounding, ask yourself what happens when that rounding bumps things up to the next second, minute, hour, day, week, month, or ever year. Don’t use anything that involves an implicit or explicit conversion to DATETIME for this.
Also, don’t use the concatenation methods that involve a string conversion. It’ll make your code about twice as slow and will become a part of the “Death by a 1000 small performance issues” that our servers frequently suffer from.
One of the correct ways to do this is with the following formula. Of course, the DECLARE is to just setup the test data.
DECLARE @dt DATE = ‘2020-12-31′
,@tm TIME = ’23:59:59.9991234′
;
SELECT Combined = DATEADD(dd,DATEDIFF(dd,’1900’,@dt),CONVERT(DATETIME2,@tm))
;
That returns a DATETIME2 value of 2020-12-31 23:59:59.9991234.
If you do the same thing with the originally posted formula, you get a nasty surprise both for resolution and the fact the every date part in the entire date was bumped up to the beginning of the next year.
DECLARE @dt DATE = ‘2020-12-31′
,@tm TIME = ’23:59:59.9991234’
;
SELECT DATEADD(DAY,DATEDIFF(DAY, 0, @dt),CAST(@tm AS DATETIME)) AS [CombineDateTime]
And that returns something that looks nothing like the original …
2021-01-01 00:00:00.000
Try it and see.
Reply
Jeff Moden
March 30, 2023 10:05 pm
p.s. If you use the same data as the example I posted for the first method, you find that the second method in this article has an identical issue because of the use of DATETIME. The second method will work correctly if you change DATETIME to DATETIME2.
Reply