Tags

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


SQL Puzzle | The Date Format (yyyy-MM-dd hh:mm:ss) Puzzle

In this puzzle you have to take getdate as input and then remove time from it and then add 23:59:59 into that date and provide the output i format yyyy-MM-dd hh:mm:ss.

Please check out the sample input values and sample expected output below.

Sample Input

CurrentDate
2017-10-07 13:17:22.040

Expected Output

CurrentDate ChangedFormat
2017-10-07 13:18:16.427 2017-10-07 23:59:59

Script

Use below script to create table and insert sample data into it.

--

SELECT GETDATE() AS CurrentDate

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


--


SELECT GETDATE() CurrentDate , LEFT(CONVERT(VARCHAR, GETDATE(), 20), 10) + ' 23:59:59' ChangedFormat

--

Output


/*------------------------
SELECT GETDATE() CurrentDate , LEFT(CONVERT(VARCHAR, GETDATE(), 20), 10) + ' 23:59:59' ChangedFormat
------------------------*/
CurrentDate             ChangedFormat
----------------------- -------------------
2017-10-07 13:19:15.800 2017-10-07 23:59:59

(1 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements