Tags

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


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.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

Advertisements