SQL Puzzle | How to get Date Part & Time Part from DateTime in Sql Server [Multiple Methods]

In this puzzle you have to get time part and date part from a datetime variable.

Please check out the sample input and the expected output below-

Sample Input

2016-06-26 08:29:33.673 [SELECT GETDATE()]

Expected Output

Dt
2016-06-26
Tm
08:41:35.2230000

Rules/Restrictions

• The solution should be should use SINGLE “SELECT” statement.

Script | use below script to create table & insert some sample data

 ``` -- SELECT GETDATE() -- ```

Getting Date Part from DateTime

Solution 1 | Assignment to Date datatype

 ``` -- DECLARE @ DATE = '' SELECT @ = GETDATE() SELECT @ Dt GO -- ```

Solution 2 | Using Cast

 ``` -- DECLARE @ DATE = '' SELECT @ = GETDATE() SELECT CAST(@ AS DATE) Dt GO -- ```

Solution 3 | Using Convert & 101

 ``` -- DECLARE @ DATE = '' SELECT @ = GETDATE() SELECT CONVERT(VARCHAR(10), @ , 101 ) Dt GO -- ```

Solution 4 | Using Convert

 ``` -- DECLARE @ DATE = '' SELECT @ = GETDATE() SELECT CONVERT(DATE,@) Dt GO -- ```

Getting Time Part from DateTime

Solution 1 | Assignment to Time datatype

 ``` -- DECLARE @ TIME = '' SELECT @ = GETDATE() SELECT @ Tm GO -- ```

Solution 2 | Using Cast

 ``` -- DECLARE @ TIME = '' SELECT @ = GETDATE() SELECT CAST(@ AS TIME) Tm GO -- ```

Solution 3 | Using Convert & 108

 ``` -- DECLARE @ TIME = '' SELECT @ = GETDATE() SELECT CONVERT(VARCHAR(8), @ , 108 ) Tm Go -- ```

Solution 4 | Using Convert

 ``` -- DECLARE @ TIME = '' SELECT @ = GETDATE() SELECT CONVERT(TIME,@) Tm GO -- ```

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