Tag: CURRENT_TIMESTAMP in SQL Server

SQL

CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE() and SYSDATETIME() in SQL Server
CURRENT_TIMESTAMP – Returns the current system date and time.

GETDATE() – Returns the current system date and time.

GETUTCDATE() – Returns the current UTC date and time.

SYSDATETIME() – Returns the date and time.

Precisely, let us quickly see the output of each one as below. 

Select current_timestamp,getdate(),getutcdate(),sysdatetime()

Whats the difference between CURRENT_TIMESTAMP and GETDATE()?

CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE is the T-SQL version of that same function.It is to be mentioned that CURRENT_TIMESTAMP is getting converted to GETDATE() when creating the object within SSMS. Both functions retrieve their value from the operating system in the same way. CURRENT_TIMESTAMP is the recommended usage because it is portable to any ANSI compliant database, where as GETDATE() is not. So, its more of a project requirement and for future demands or compatibility, few people would go with CURRENT_TIMESTAMP over GETDATE().

About performance – I have not seen any notable or any difference between thee two, it may be due to the fact that SQL-SERVER engine is converting CURRENT_TIMESTAMP to GETDATE(). 

Select CURRENT_TIMESTAMP,getdate() Where CURRENT_TIMESTAMP=getdate()

Whats the difference between GETDATE() and GETUTCDATE()?

The difference between GETDATE() and GETUTCDATE() is in timezone. GETUTCDATE() returns current time and date in UTC (Universal Time Coordinate) or GMT timezone. The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

Whats the difference between GETDATE() and SYSDATETIME()?

If you want more precision, yes SYSDATETIME() is your friend. SYSDATETIME() which has millisecond precision. It actually returns a datetime2(7) value, but the last four decimals are just noise to me. I hardly seen people are using it.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Date & Time, SQL

CURRENT_TIMESTAMP in SQL Server

CURRENT_TIMESTAMP is a system function to retrieve the current system date and time. CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE is the T-SQL version of that same function. That says it is a non-deterministic function. If you are very specific to ANSI comaptibility or thinking of heterogenous system support, I would suggest to use CURRRENT_TIMESTAMP instead of GETDATE(). As an envision, I prefer to use CURRENT_TIMESTAMP over GETDATE() personally wherever possible.

Interesting facts:

1. When we create a default constaint with CURRENT_TIMESTAMP, by default, SSMS properties are showing GETDATE().


My thought: This could be because SSMS properties have nothing to do with ANSI compatibility nor hetrogeneous support.

2. Tool tip text of the function adds parenthesis – ()

My thought: I have no clue why Microsoft has made this so.Its more concerned to me the function with “()” gives an error as below.