Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Multiple ways to convert datetime to date with 00:00:00.000 Time | SQL Interview Question

In this puzzle . Please see the sample input data and the expected output.

Sample Input

Id Dt
1 2018-07-16 11:24:50.090
2 2018-07-15 11:24:50.090
3 2018-07-14 11:24:50.090
4 2018-07-13 11:24:50.090
5 2018-07-12 11:24:50.090

Expected Output

Id Dt DateWithZeroTime
1 2018-07-16 11:24:50.090 2018-07-16 00:00:00.000
2 2018-07-15 11:24:50.090 2018-07-15 00:00:00.000
3 2018-07-14 11:24:50.090 2018-07-14 00:00:00.000
4 2018-07-13 11:24:50.090 2018-07-13 00:00:00.000
5 2018-07-12 11:24:50.090 2018-07-12 00:00:00.000

Script – DDL and INSERT Sample Data

--

CREATE TABLE MyDate
(
	Id INT,
	Dt DATETIME
)
GO

INSERT INTO MyDate VALUES
(1,GETDATE()),
(2,GETDATE()-1),
(3,GETDATE()-2),
(4,GETDATE()-3),
(5,GETDATE()-4)
GO

SELECT * FROM MyDate 
GO

--

SOLUTION 1

--

SELECT *,DATEADD(Day, 0, DATEDIFF(Day, 0, Dt)) [DateWithZeroTime]
FROM MyDate
GO

--

OUTPUT – 1

--

Id          Dt                      DateWithZeroTime
----------- ----------------------- -----------------------
1           2018-07-16 11:24:50.090 2018-07-16 00:00:00.000
2           2018-07-15 11:24:50.090 2018-07-15 00:00:00.000
3           2018-07-14 11:24:50.090 2018-07-14 00:00:00.000
4           2018-07-13 11:24:50.090 2018-07-13 00:00:00.000
5           2018-07-12 11:24:50.090 2018-07-12 00:00:00.000

(5 rows affected)


--

SOLUTION 2

--

SELECT *,CAST(FLOOR(CAST(dt AS FLOAT)) AS DATETIME) [DateWithZeroTime]
FROM Mydate
GO

--

OUTPUT – 2

--

Id          Dt                      DateWithZeroTime
----------- ----------------------- -----------------------
1           2018-07-16 11:24:50.090 2018-07-16 00:00:00.000
2           2018-07-15 11:24:50.090 2018-07-15 00:00:00.000
3           2018-07-14 11:24:50.090 2018-07-14 00:00:00.000
4           2018-07-13 11:24:50.090 2018-07-13 00:00:00.000
5           2018-07-12 11:24:50.090 2018-07-12 00:00:00.000

(5 rows affected)


--

SOLUTION 3

--

SELECT *,CAST(CAST(dt AS DATE) AS DATETIME) [DateWithZeroTime]
FROM Mydate
GO

--

OUTPUT – 3

--

Id          Dt                      DateWithZeroTime
----------- ----------------------- -----------------------
1           2018-07-16 11:24:50.090 2018-07-16 00:00:00.000
2           2018-07-15 11:24:50.090 2018-07-15 00:00:00.000
3           2018-07-14 11:24:50.090 2018-07-14 00:00:00.000
4           2018-07-13 11:24:50.090 2018-07-13 00:00:00.000
5           2018-07-12 11:24:50.090 2018-07-12 00:00:00.000

(5 rows affected)


--

SOLUTION 4

--

SELECT *,CAST(CONVERT(VARCHAR,dt,102) AS DATETIME) [DateWithZeroTime]
FROM Mydate
GO

--

OUTPUT – 4

--

Id          Dt                      DateWithZeroTime
----------- ----------------------- -----------------------
1           2018-07-16 11:24:50.090 2018-07-16 00:00:00.000
2           2018-07-15 11:24:50.090 2018-07-15 00:00:00.000
3           2018-07-14 11:24:50.090 2018-07-14 00:00:00.000
4           2018-07-13 11:24:50.090 2018-07-13 00:00:00.000
5           2018-07-12 11:24:50.090 2018-07-12 00:00:00.000

(5 rows 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.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance 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/

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

Advertisements