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:

• How to get Date Part only from DateTime in Sql Server
• How to get Day, Month and Year Part from DateTime in Sql Server
• Difference between DateTime and DateTime2 DataType

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:

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:

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:

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

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

RESULT:

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:

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:

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:

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:

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:

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: