Rajendra Gupta
Create table with default values

SQL Commands to check current Date and Time (Timestamp) in SQL Server

October 7, 2022 by

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).

SQL GETDATE

You can add or subtract dates from the GETDATE() functions. For example, GETDATE()-1 returns yesterday Timestamp while GETDATE()+1 returns tomorrows timestamp.

Get current and previous day values

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.

AT TIME ZONE

Query the system table below to get a list of supported Azure time zones.

TimeZone values

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.

CURRENT_TIMESTAMP

As shown below, we can replace GETDATE() with the CURRENT_TIMESTAMP with the AT TIME ZONE function to retrieve desired time zone value.

CURRENT_TIMESTAMP with AT TIME ZONE

SYSDATETIME()

The SYSDATETIME() function returns a more precise value in a fraction of a second. The returned value datatype is datetime2 with 7 precisions.

SYSDATETIME

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.

GETUTCDATE

  • 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.

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.

SYSDATETIMEOFFSET

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.

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.

Different DateTime functions

Similarly, as shown below, we can use the time argument in the CONVERT() function to extract the timestamp part from the output.

Get time values from the DateTime functions

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.

Create table with default values

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.

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.

Stored procedure error

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.

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.

How to specify parameter value in Stored procedure

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.

Rajendra Gupta
168 Views