GETDATE vs GETUTCDATE vs SYSDATETIME vs CURRENT_TIMESTAMP in SQL Server

GETDATE vs GETUTCDATE vs SYSDATETIME vs CURRENT_TIMESTAMP in SQL Server

1. GETDATE() 
returns a datetime value.
is the T-SQL version of that same function.

2. SYSDATETIME() 
returns a datetime2(7) value.
SYSDATETIME() has more fractional seconds precision than GETDATE().

3. CURRENT_TIMESTAMP 
is an ANSI SQL function
CURRENT_TIMESTAMP is getting converted to GETDATE() when creating the object within SSMS

4. GETUTCDATE();
Return the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.

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. 
• The datetime2 data type also has a larger date range and optional user-specified precision.

Which One Should I Use?
Microsoft recommends that we use datetime2 with our date/time values. This data type aligns with the SQL standard, and is more portable than datetime.
CURRENT_TIMESTAMP is the recommended usage because it is portable to any ANSI compliant database, where as GETDATE() is not.