Difference between CURRENT_TIMESTAMP vs GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server

Introduction

In this article, we will discuss the difference between CURRENT_TIMESTAMP , GETDATE() , SYSDATETIME() GETUTCDATE() in SQL Server. Even though all four SQL Server function returns the current date-time in SQL Server, there are some subtle differences between them. The main difference between GETDATE() and SYSDATETIME() is that GETDATE returns current date and time as DATETIME but SYSDATETIME returns a DATETIME2 value, which is more precise.

SELECT 
	CURRENT_TIMESTAMP AS CurrentTimeStamp, 
	GETDATE() AS GetDates,
	GETUTCDATE() AS GetUTCDates, 
	SYSDATETIME() AS SysDateTimes

CURRENT_TIMESTAMP vs GETDATE()

• CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE() is the T-SQL version of that same function.
• 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.

GETDATE() vs GETUTCDATE()

• The difference between GETDATE() and GETUTCDATE() is in time zone.
• The GETDATE() function return current date and time in the local time zone, the time zone where your database server is running, but GETUTCDATE() return current time and date in UTC (Universal Time Coordinate) or GMT time zone.

GETDATE() vs SYSDATETIME()

• If you want more accurate date and timings, then you have to go with SYSDATETIME().
• SYSDATETIME() which has millisecond precision. It actually returns a datetime2(7) value and local time, while later returns DATETIME value and UTC time.

Points to Remember

• The GETDATE() returns the local time of the server where your database is running. So, if you are connecting to a remote SQL Server using SQL Server Management Studio or Azure Data Studio, the time returned by this method would be different than your local system time. The value is returned as the DATETIME data type.
• GETUTCDATE() return the current date-time in the UTC time zone, also known as GMT. It also returns the result as a DATETIME data type. You can use this method to store the timestamp that is independent of time zones.
• SYSDATETIME() also returns local date-time, similar to GETDATE. This means if you are connected to a remote Microsoft SQL Server then it would return a different value then your current system date-time, but it returns a more precise DATETIME2 value. This means, use GETDATE() if you are fine with DATETIME precision and use SYSDATETIME() if you need a more precise current date and time value.

Conclusion

In this article, we discussed about the difference between these Date and Time and I hope you all found this article useful. Please do share your feedback in the comment section.

Also Consider to read,

• Change Schema Name of Table in MS SQL Server
• Identifying object dependencies in SQL Server using SP_DEPENDS
• Select Names Starting With Vowels in MS SQL Server
• Display Line Numbers in a SQL Server Management Studio Query Window (SSMS)