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

One of the common question on Microsoft SQL Server interview is, what is the difference between GETDATE(), SYSDATETIME(), and GETUTCDATE(). Even though all three SQL Sever 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. The difference between GETDATE() and GETUTCDATE() is in timezone, the GETDATE() function return current date and time in the local timezone, the timezone where your database server is running, but GETUTCDATE() return 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 same, former return DATETIME2 value and local time, while later returns DATETIME value and UTC time. Please read Microsoft SQL Server 2012 T-SQL Fundamentals to learn more about these three date and time functions in Microsoft SQL server




GETDATE vs GETUTCDATE vs SYSDATETIME in SQL Server

Let's see an SQL Server query to understand the there difference in more detail.  If you run 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.  Please see Querying Microsoft SQL Server 2012 to learn more about how to deal with date and time information in Microsoft SQL Server.




Summary

1) The GETDATE() return 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 DATETIME data type.


2) GETUTCDATE() return the current date time in UTC timezone, also known as GMT. It also returns the result as 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. Which 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 return a more precise DATETIME2 value. Which means, use GETDATE() if you are fine with DATETIME precision and use SYSDATETIME() if you need more precise current date and time value.

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

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


That's all about the difference between GETDATE(), GETUTCDATE() and SYSDATETIME() function in Microsoft SQL Server. Apart from these key difference, 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 2012 or Microsoft SQL Server 2012 T-SQL Fundamentals to learn SQL Server fundamentals, both are great books and helped me immensely to work effectively with Microsoft SQL Server.

GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server



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 replace null with empty String in SQL Server? (solution)
  • How to find the length of String in MSSQL? (solution)
  • How to add columns on 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 GETDATE() function in SQL Server? (answer)
  • The difference between SQL queries in Oracle and Microsoft SQL Server? (answer)
  • How to increase the length of 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)

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




No comments :

Post a Comment