SQL Puzzle | Multiple ways to add Date and Time to a DATETIME | SQL Interview Question

This was asked to one of my developer by a Client in a technical interview so I thought I must share it with you guys. In this puzzle you have 2 variables one is DATE and other is TIME and you have to add both to get a DATETIME. Please check out the sample input and the expected output.

Sample Input

Expected Output

Script – DDL and INSERT Sample Data

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'

--

–SOLUTION 1

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT CAST(@Dt AS DATETIME) + CAST(@Tm AS DATETIME) [DateTime]
GO

--

OUTPUT – 1

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 2

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT 
	DATEADD(MILLISECOND, DATEDIFF(MILLISECOND, '00:00:00', @Tm), CONVERT(DATETIME, @Dt))
	[DateTime]
GO

--

OUTPUT – 2

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 3

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT DATEADD(DAY,DATEDIFF(DAY, 0, @Dt),CAST(@Tm AS DATETIME))
	 [DateTime]
GO

--

OUTPUT – 3

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 4

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT CAST(CONCAT(@Dt,' ',CAST(@Tm AS VARCHAR(12))) AS DATETIME)
	[DateTime]
GO

--

OUTPUT – 4

--


DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com