Difference Betwixt Getdate() Vs Sysdatetime() Vs Getutcdate() Inwards Sql Server

One of the common question on Microsoft SQL Server interview is, what is the divergence betwixt GETDATE(), SYSDATETIME(), in addition to GETUTCDATE(). Even though all 3 SQL Sever part returns the electrical flow engagement fourth dimension inward SQL Server, at that topographic point are around subtle differences betwixt them. The primary divergence betwixt GETDATE() in addition to SYSDATETIME() is that GETDATE returns electrical flow engagement in addition to fourth dimension every bit DATETIME but SYSDATETIME returns a DATETIME2 value, which is to a greater extent than precise. The divergence betwixt GETDATE() in addition to GETUTCDATE() is inward timezone, the GETDATE() part furnish electrical flow engagement in addition to fourth dimension inward the local timezone, the timezone where your database server is running, but GETUTCDATE() furnish electrical flow fourth dimension in addition to engagement inward UTC (Universal Time Coordinate) or GMT timezone.


Since both GETDATE in addition to GETUTCDATE furnish a DATETIME value, thence the divergence betwixt SYSDATETIME() in addition to GETUTCDATE() is same, sometime furnish DATETIME2 value in addition to local time, spell afterward returns DATETIME value in addition to UTC time. Please read Microsoft SQL Server 2012 T-SQL Fundamentals to larn to a greater extent than virtually these 3 engagement in addition to fourth dimension functions inward Microsoft SQL server

GETDATE vs GETUTCDATE vs SYSDATETIME inward SQL Server

Let's encounter an SQL Server query to empathise the at that topographic point divergence inward to a greater extent than detail.  If you lot run next SQL query on your Microsoft SQL Server management studio, you lot tin empathise the divergence betwixt them past times 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 tin encounter that GETDATE returns local time, London UK, but GETUTCDATE returns UTC fourth dimension which is 1 hr behind U.K. fourth dimension (GMT + 1). You tin likewise encounter the engagement returned past times SYSDATETIME is to a greater extent than precise, upward to vii decimal places than the value returned past times GETDATE() in addition to GETUTCDATE() which is but up-to 3 decimal places accurate.  Please encounter Querying Microsoft SQL Server 2012 to larn to a greater extent than virtually how to bargain amongst engagement in addition to fourth dimension information inward Microsoft SQL Server.

Summary

1) The GETDATE() furnish local fourth dimension of the server where your database is running. So, if you lot are connecting to a remote SQL Server using SQL Server Management Studio, the fourth dimension returned past times this method would hold upward unlike than your local organisation time. The value is returned every bit DATETIME information type.


2) GETUTCDATE() furnish the electrical flow engagement fourth dimension inward UTC timezone, likewise known every bit GMT. It likewise returns the termination every bit DATETIME information type. You tin work this method to shop the timestamp that is independent of timezones.


3) SYSDATETIME() likewise returns local engagement time, similar to GETDATE. Which agency if you lot are connected to a remote Microsoft SQL Server so it would furnish a unlike value so your electrical flow organisation engagement time, but it furnish a to a greater extent than precise DATETIME2 value. Which means, work GETDATE() if you lot are fine amongst DATETIME precision in addition to work SYSDATETIME() if you lot postulate to a greater extent than precise electrical flow engagement in addition to fourth dimension value.

Here is likewise a prissy slide to summarize the divergence betwixt diverse engagement in addition to fourth dimension part of Microsoft SQL Server 2012:

That's all virtually the difference betwixt GETDATE(), GETUTCDATE() in addition to SYSDATETIME() part inward Microsoft SQL Server. Apart from these key difference, but squall upward that these are non-deterministic functions i.e. they furnish unlike values each fourth dimension you lot call, thence views in addition to expressions that reference this column cannot hold upward indexed. I likewise recommend reading either Querying Microsoft SQL Server 2012 or Microsoft SQL Server 2012 T-SQL Fundamentals to larn SQL Server fundamentals, both are slap-up books in addition to helped me immensely to piece of work effectively amongst Microsoft SQL Server.

Other SQL Server articles you lot may like

• How to compare Dates inward Microsoft SQL Server? (solution)
• How to bring together 3 tables inward 1 SQL Query? (tutorial)
• How to supplant zippo amongst empty String inward SQL Server? (solution)
• How to discovery the length of String inward MSSQL? (solution)
• How to add together columns on existing tabular array inward Microsoft SQL Server? (solution)
• What is the divergence betwixt row_number(), rank(), in addition to dense_rank() inward SQL? (answer)
• How to acquire but engagement or fourth dimension from GETDATE() part inward SQL Server? (answer)
• The difference between SQL queries inward Oracle in addition to Microsoft SQL Server? (answer)
• How to increase the length of existing varchar column inward SQL Server? (solution)
• SQL query to discovery all tabular array names inward a database? (query)
• How to delete from a tabular array using bring together inward SQL? (tutorial)
• What is the divergence betwixt WHERE in addition to HAVING clause inward SQL? (answer)

References
Date in addition to Time Data types in addition to Functions (Transact-SQL)

Share this post