How to get Time, Hour, Minute, Second and Millisecond Part from DateTime in Sql Server

Many a times we may need to get the Time, Hour, Minute, Second and Millisecond Part from DateTime in Sql Server. In this article we will see how we can get these parts of the DateTime in Sql Server.

You may like to read the other popular articles on Date and Time:

1. TIME part of DateTime in Sql Server

Following demos shows how to get some of the commonly required Time Part format from a DateTime.

Demo 1: Time in the 24-hour format hh:mi:ss

SELECT GETDATE() 'Today', 
       CONVERT(VARCHAR(8), GETDATE(), 108) 'hh:mi:ss'

RESULT:
TimePart1

Demo 2: Time in the 24-hour format hh:mi:ss:mmm

SELECT GETDATE() 'Today',
	CONVERT(VARCHAR(12),GETDATE(),114) 'hh:mi:ss:mmm'

RESULT:
TimePart2

Demo 3: Time in the 12-hour format hh:mi:ss:mmmAM (or PM)

SELECT  GETDATE() 'Today',
 RIGHT(CONVERT(VARCHAR(26), GETDATE(), 109),14) 'hh:mi:ss:mmmAM (or PM)'

RESULT:
TimePart3

Demo 4: Time in the 12-hour format hh:miAM (or PM)

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

RESULT:
TimePart4

Demo 5: Time in the 24-hour format hh:miAM (or PM)

SELECT  GETDATE() 'Today',
	CONVERT(VARCHAR(5), GETDATE(), 108) + 
	(CASE WHEN DATEPART(HOUR, GETDATE()) > 12 THEN 'PM' 
		ELSE 'AM' 
	END) 'hh:miAM (or PM)'

RESULT:
TimePart5

Demo 6: Time in the 24-hour format  hh:mm:ss.nnnnnnn

Note this script will work in sql Server 2008 and above as here I am using TIME datatype and SYSDATETIME() functions which were introduced in Sql Server 2008.

SELECT   GETDATE() 'Today',
		CAST(SYSDATETIME() AS TIME) 'hh:mm:ss.nnnnnnn'

RESULT:
TimePart6

2. HOUR part of the DateTime in Sql Server

We can use DATEPART() function to get the HOUR part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as hour or hh.

SELECT GETDATE() 'Today', DATEPART(hour,GETDATE()) 'Hour Part'
SELECT GETDATE() 'Today', DATEPART(hh,GETDATE()) 'Hour Part'

RESULT:
Hour Part of DateTime in Sql Server

3. MINUTE part of the DateTime in Sql Server

We can use DATEPART() function to get the MINUTE part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as minute or mi or n.

SELECT GETDATE() 'Today', 
       DATEPART(minute,GETDATE()) 'Minute Part'
SELECT GETDATE() 'Today', DATEPART(mi,GETDATE()) 'Minute Part'
SELECT GETDATE() 'Today', DATEPART(n,GETDATE()) 'Minute Part'

RESULT:
Minute Part of DateTime in Sql Server

4. SECOND part of the DateTime in Sql Server

We can use DATEPART() function to get the SECOND part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as second or ss or s.

SELECT GETDATE() 'Today', 
       DATEPART(second,GETDATE()) 'Second Part'
SELECT GETDATE() 'Today', DATEPART(ss,GETDATE()) 'Second Part'
SELECT GETDATE() 'Today', DATEPART(s,GETDATE()) 'Second Part'

RESULT:
Second Part of DateTime in Sql Server

5. MILLISECOND part of the DateTime in Sql Server

We can use DATEPART() function to get the MILLISECOND part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as millisecond or mi .

SELECT GETDATE() 'Today', 
       DATEPART(millisecond,GETDATE()) 'MilliSecond Part'
SELECT GETDATE() 'Today', 
       DATEPART(ms,GETDATE()) 'MilliSecond Part'

RESULT:
MilliSecond Part Of DateTime in Sql Server

19 thoughts on “How to get Time, Hour, Minute, Second and Millisecond Part from DateTime in Sql Server

  1. That was extremely helpful. This is how to replace a null date with a new date and the current time: ISNULL(,CAST((‘2010-1-1 ‘+CONVERT(VARCHAR(8), GETUTCDATE(), 108)) AS DATETIME)). This is useful when you store as utc and convert to local time for display.

  2. Jeje! Amigo, eres un kurdo.

    Excelente tutorial. Buena explicación y dinámica.

    Me sirvió. Agradezco tu consejo.

    Saludos.

  3. Dear Mr. Biradar, kindly need your help (I’m sorry for my bad English),
    I’m already get result time using ‘Demo 1’ for example Time1 and Time2, but the datatype result of ‘Demo 1’ is in varchar.
    My Question is how to subtract between two times ?
    Thanks for your help
    eko

  4. I have float value 1.09796836411745 and need output in time 02:21:0463 …

    how to get output using sql select ..i.e. select floattotime from table..

  5. I have been searching for days trying to find something to convert time from 24 hour to 12 hour. Most people said it couldn’t be done or they provided solutions that didn’t work. This did the trick so thank you so much!!!

Leave a Reply

Your email address will not be published. Required fields are marked *