Tag Archives: Current TIME using CURRENT_TIMESTAMP

How to get current TIME in Sql Server

Sql Server, , , , , , ,

Many times we come across a scenario where we need to get the current TIME. There are multiple ways to get this information in Sql Server, here I am listing out few of them. This article also lists out the various formats in which we can get the current TIME

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

Approach 1: Get current TIME Using GETDATE() funtion

GETDATE() function returns the current Date and Time from the system on which the Sql Server is installed/running. We can fetch the TIME part from the DATETIME value returned from the GETDATE() function as below:

SELECT CONVERT(TIME, GETDATE()) 
      AS 'Current TIME using GETDATE()'

RESULT:
current-time-using-getdate-in-sql
[ALSO READ] How to get Current DATE and TIME in Sql Server

Approach 2: Get current TIME Using CURRENT_TIMESTAMP funtion

CURRENT_TIMESTAMP function is the ANSI SQL equivalent of the GETDATE() function. We can fetch the TIME part from the DATETIME value returned from the CURRENT_TIMESTAMP function as below:

SELECT CONVERT (TIME, CURRENT_TIMESTAMP) 
  AS 'Current TIME CURRENT_TIMESTAMP'

RESULT:
current-time-using-current_timestamp-in-sql

From the above result we can see that the TIME returned by the CURRENT_TIMESTAMP function is same as that of the TIME returned by using the GETDATE() function as shown in the previous example.

[ALSO READ] How to get Date Part only from DateTime in Sql Server

Approach 3: Get current TIME using SYSDATETIME() funtion

SYSDATETIME() function can also be used to get the current TIME of the computer on which the instance of SQL Server is running. SYSDATETIME() function provides more fractional seconds precision compared to the GETDATE() function. We can fetch the TIME part from the DATE and TIME value returned from the SYSDATETIME() function as below:

SELECT SYSDATETIME() 'Current TIME using SYSDATETIME()'

RESULT:
current-time-using-sysdatetime-in-sql

From the above result we can see that the precision of the current TIME returned by using the SYSDATETIME function is better than that of the TIME part returned by using the GETDATE() or CURRENT_TIMESTAMP function.

[ALSO READ] How to get Day, Month and Year Part from DateTime in Sql Server

Current TIME in Various Formats

Below are the couple of examples of retrieving current TIME in various formats:

1. Current TIME in the 24-hour format hh:mi:ss

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

RESULT:
current-time-in-hh-mi-ss-format
2. Current TIME in the 24-hour format hh:mi:ss:mmm

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

RESULT:
current-time-in-hh-mi-ss-mmm-format
[ALSO READ] How to get month name from date in Sql Server
3. Current TIME in the 12-hour format hh:mi:ss:mmmAM

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

RESULT:
current-time-in-hh-mi-ss-mmm-am-pm-format
4. Current TIME in the 12-hour format hh:miAM (or PM)

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

RESULT:
current-time-in-12-hour-hh-mi-am-pm-format
5. Current TIME in the 24-hour format hh:miAM (or PM)

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

RESULT:
current-time-in-24-hour-hh-mi-am-pm-format
[ALSO READ] How to get Day or Weekday name from date in Sql Server