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

Date Time
2018-08-10 07:48:46.4566667

Expected Output

DateTime
2018-08-10 07:48:46.457

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 🙂

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.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

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:

Enjoy !!! Keep Learning

Http://MsbiSkills.com