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 asmillisecond 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s