This article explores different SQL Commands (functions) to return the current Date and Time (Timestamp) in SQL Server.
Introduction
In database applications, the current date and time are used in many ways. Whether it is creating audit logs, Sales records, database triggers, or just because you need the current date and time, knowing different ways to retrieve the current date and time can be very useful. This article will discuss various T-SQL current date functions, when you would use them, and how they should be used.
This article explores DateTime SQL Commands (functions) for SQL Server, Azure SQL Database, Managed instance(MI), and Azure Synapse Analytics.
- GETDATE()
- CURRENT_TIMESTAMP
- SYSDATETIME
- GETUTCDATE
- SYSUTCDATETIME
- SYSDATETIMEOFFSET
Let’s begin exploring these articles and understand the difference between them.
SQL Server GETDATE() function
The GETDATE() SQL Commands (function) returns the system timestamp without specifying the time zone offset. It retrieves the value according to the underlying computer(server) time zone. The returned value data type is DateTime.
However, running the GETDATE() function on Azure SQL Database and Azure Synapse Analytics returns the UTC (Universal Time Coordinate).
You can add or subtract dates from the GETDATE() functions. For example, GETDATE()-1 returns yesterday Timestamp while GETDATE()+1 returns tomorrows timestamp.
|
1 2 3 4 5 |
SELECT getdate()-1 AS Yesterday, getdate() AS Today, getdate()+1 AS Tomorrow |
If we require interpreting returned value non-UTC time zone for Azure SQL Database or SQL Server, use the AT TIME ZONE function.
For example, suppose we want to get India Standard Time(IST) time zone value from the getdate() function. The following script defines the current time zone as UTC and then converts it to the desired time zone value.
|
1 2 3 |
SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time' |
Query the system table below to get a list of supported Azure time zones.
|
1 2 3 |
SELECT name AS TimeZone, Current_UTC_offset FROM sys.time_zone_info |
CURRENT_TIMESTAMP
The CURRENT_TIMESTAMP SQL Commands (function) returns the system timestamp similar to the GETDATE() function. It is the ANSI equivalent of the GETDATE() function and can be used interchangeably in T-SQL statements.
As shown below, we can replace GETDATE() with the CURRENT_TIMESTAMP with the AT TIME ZONE function to retrieve desired time zone value.
|
1 2 3 |
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time' |
SYSDATETIME()
The SYSDATETIME() function returns a more precise value in a fraction of a second. The returned value datatype is datetime2 with 7 precisions.
GETUTCDATE() and SYSUTCDATETIME()
Suppose you have the requirement to get a UTC timestamp despite your system time zone. In this case, you can use the SQL Commands (function) GETUTCDATE() as shown below.
- Return value data type: DateTime
- Time zone offset included: No
The SYSUTCDATE() also returns the UTC zone value with higher precision. Its returned value datatype is DateTime2 with precision 7.
|
1 2 3 |
SELECT SYSUTCDATETIME() |
SYSDATETIMEOFFSET()
The SYSDATETIMEOFFSET() SQL Commands (function) returns a value based on the underlying operating system time zone. It includes the higher precision along with the time zone offset.
As shown below, it includes a +00:00 time zone offset that shows that it is a UTC.
Query to check outputs returned by different DateTime functions
The following query combines all SQL Server DateTime functions in a SELECT statement. You can run the below query to compare the returned values. It returns the value from the GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME, SYSDATETIMEOFFSET, GETUTCDATE, SYSUTCDATETIME as shown below.
|
1 2 3 4 5 6 7 8 9 |
SELECT GETDATE() AS [GETDATE()] ,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP] ,SYSDATETIME() AS [SYSDATETIME()] ,SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()] ,GETUTCDATE() AS [GETUTCDATE()] ,SYSUTCDATETIME() AS [SYSUTCDATETIME()] ; |
In my Azure SQL Database, it returned the following values.
- GETDATE(): 2021-12-25 02:50:40.767
- CURRENT_TIMESTAMP: 2021-12-25 02:50:40.767
- SYSDATETIME():2021-12-25 02:50:40.7500000
- SYSDATETIMEOFFSET(): 2021-12-25 02:50:40.7500000 +00:00
- GETUTCDATE(): 2021-12-25 02:50:40.753
- SYSUTCDATETIME: 2021-12-25 02:50:40.7534774
In many cases, we do not need timestamp or offset values. In this case, we can utilize CONVERT() function, and all functions return the date as shown below.
|
1 2 3 4 5 6 7 8 9 |
SELECT CONVERT (date,GETDATE()) AS [GETDATE()] ,CONVERT (date,CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP] ,CONVERT (date,SYSDATETIME()) AS [SYSDATETIME()] ,CONVERT (date,SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET()] ,CONVERT (date,GETUTCDATE()) AS [GETUTCDATE()] ,CONVERT (date,SYSUTCDATETIME()) AS [SYSUTCDATETIME()] ; |
Similarly, as shown below, we can use the time argument in the CONVERT() function to extract the timestamp part from the output.
|
1 2 3 4 5 6 7 8 9 |
SELECT CONVERT (time,GETDATE()) AS [GETDATE()] ,CONVERT (time,CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP] ,CONVERT (time,SYSDATETIME()) AS [SYSDATETIME()] ,CONVERT (time,SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET()] ,CONVERT (time,GETUTCDATE()) AS [GETUTCDATE()] ,CONVERT (time,SYSUTCDATETIME()) AS [SYSUTCDATETIME()] ; |
Let’s explore a few use cases of various DateTime functions in SQL Server and Azure SQL Database.
The following example creates a table named [DemoSQLTable] with a few columns and their default values from the DateTime function – GETDATE(), CURRENT_TIMESTAMP, and SYSDATETIME(). Once we insert a record without any explicit value, it retrieves data from these DateTime functions and stores them into their respective columns.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Create Table DemoSQLTable ( id int, myGETDATE smalldatetime default GETDATE(), myCurrentTimeStamp datetime default CURRENT_TIMESTAMP, mySYSDATETIME datetime2 default SYSDATETIME() ); GO insert into DemoSQLTable (ID) values (1); GO Select * from DemoSQLTable; |
Can we use the DateTime function as stored procedure parameters?
We often require data from database tables that satisfy a date range or specific date-time. For example, if we store Customers’ Sales records in a table, we might be interested in knowing orders placed today or a specific month or year.
Let’s create a stored procedure using the following query for the demonstration. It defines a parameter @MyDateTime having data type DATETIME. Later, we want to filter records from the [SalesLT].[SalesOrderDetail] table based on the parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE PROC Test_DateTime_Proc @MyDateTime DATETIME as SELECT [SalesOrderID] ,[SalesOrderDetailID] ,[OrderQty] ,[ProductID] ,[ModifiedDate] FROM [SalesLT].[SalesOrderDetail] WHERE [ModifiedDate]=@MyDateTime |
We want to use DateTime functions for supplying values for the parameter @MyDateTime. If we directly pass the DateTime function for a parameter value, it returns an error, as shown below.
|
1 2 3 |
EXEC Test_DateTime_Proc @MyDateTime=getdate() |
To execute the stored procedure with the DateTime function as a parameter value, we can declare a variable and store the function output. For example, in T-SQL, we declare a parameter @I and set its value using the GETDATE() function.
|
1 2 3 4 5 |
Declare @I datetime = getdate() exec Test_DateTime_Proc @MyDateTime = @I; GO |
The script works fine without any error. In my case, it does not have any matching row for the predicate; therefore, it returns 0 rows affected.
Note: The DateTime functions are non-deterministic in SQL Server. Therefore, the view and expression that reference this function in a column cannot be indexed.
Conclusion
This article discussed several ways to use SQL Commands (functions) to retrieve the current date and time. I also discussed when to use these functions and how they should be used. Hopefully, this article gives you some ideas of using these functions in your applications.
If you have any queries about the DateTime functions discussed, please leave them in the comments section below. Thanks for learning, and feel free to let us know what you think in the comments.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023