Difference between GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server - Examples

One of the common questions on the Microsoft SQL Server interview is, what is the difference between GETDATE(), SYSDATETIME(), and GETUTCDATE(). Even though all three 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 the current date and time as DATETIME but SYSDATETIME returns a DATETIME2 value, which is more precise.

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

Since both GETDATE and GETUTCDATE return a DATETIME value, hence the difference between SYSDATETIME() and GETUTCDATE() is the same, former returns DATETIME2 value and local time, while the latter returns DATETIME value and UTC time. You can also explore Microsoft SQL for Beginners course to learn more about these three date and time functions in the Microsoft SQL server

GETDATE vs GETUTCDATE vs SYSDATETIME in SQL Server - Examples

Let's see an SQL Server query to understand the there difference in more detail.  If you run the following SQL query on your Microsoft SQL Server management studio, you can understand the difference between them by looking at their output:

SELECT GETDATE() AS LOCAL_TIME;
LOCAL_TIME
2015-10-16 04:37:06.593


SELECT GETUTCDATE() AS UTC_TIME;
UTC_TIME
2015-10-16 03:37:06.597


SELECT SYSDATETIME() AS CURRENT_DATE_TIME;
CURRENT_DATE_TIME
2015-10-16 12:37:06.6151778

You can see that GETDATE returns local time, London UK, but GETUTCDATE returns UTC time which is one hour behind UK time (GMT + 1). 

You can also see the Date returned by SYSDATETIME is more precise, up to 7 decimal places than the value returned by GETDATE() and GETUTCDATE() which is just up to 3 decimal places accurate. You can also explore these free SQL Server online courses to learn more about how to deal with date and time information in Microsoft SQL Server.

Summary

1. 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, the time returned by this method would be different than your local system time. The value is returned as the DATETIME data type.


2. GETUTCDATE() return the current date-time in the UTC timezone, 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 timezones.


3. 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 than 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.

Here is also a nice slide to summarize the difference between various date and time functions of Microsoft SQL Server 2012:

That's all about the difference between GETDATE(), GETUTCDATE(), and SYSDATETIME() function in Microsoft SQL Server. Apart from these key differences, just remember that these are non-deterministic functions i.e. they return different values each time you call, hence views and expressions that reference this column cannot be indexed. 

I also recommend reading either Querying Microsoft SQL Server book or joining these best SQL Server courses to learn SQL Server fundamentals, both are great books and helped me immensely to work effectively with Microsoft SQL Server.

References
Date and Time Data types and Functions (Transact-SQL)


Other SQL Server articles you may like

• How to compare Dates in Microsoft SQL Server? (solution)
• How to join three tables in one SQL Query? (tutorial)
• How to add columns to the existing table in Microsoft SQL Server? (solution)
• What is the difference between row_number(), rank(), and dense_rank() in SQL? (answer)
• How to get just date or time from the GETDATE() function in SQL Server? (answer)
• How to replace null with empty String in SQL Server? (solution)
• How to find the length of String in MSSQL? (solution)
• The difference between SQL queries in Oracle and Microsoft SQL Server? (answer)
• How to increase the length of the existing varchar column in SQL Server? (solution)
• SQL query to find all table names in a database? (query)
• How to delete from a table using join in SQL? (tutorial)
• What is the difference between WHERE and HAVING clause in SQL? (answer)

Thanks for reading this article so far. If you like my answer and explanation then please share this with your friends and colleagues. I would really appreciate that.