Get Current Timestamp in SQL Server

1. Introduction

In database operations, retrieving the current timestamp is a common task, especially when tracking the creation or modification times of records.

In this tutorial, we’ll explore various methods to obtain the current timestamp in SQL Server. While these queries have been tested on SQL Server 2022, they should work similarly in most modern versions.

2. Get Current Timestamp

SQL Server provides several functions to get the current timestamp, each serving slightly different purposes. In this section, let’s explore the various methods in detail.

2.1. Using CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function is an ANSI SQL standard that returns the current date and time down to the milliseconds, based on the system timezone. However, it doesn’t include the timezone offset in the result. Let’s look at the sample query and result:

SELECT CURRENT_TIMESTAMP AS NOW;

When we execute this query, we get the following output:

+--------------------------+
|           NOW            |
+--------------------------+
| 2024-06-15 19:48:25.887  |
+--------------------------+

Some database clients might display the timestamp without milliseconds precision, even though the data includes them. In such cases, we can confirm the presence of milliseconds by explicitly formatting the output:

SELECT CONVERT(VARCHAR(50), CURRENT_TIMESTAMP, 121)

This formatted string makes it clear that the milliseconds are included in the timestamp during display. However, if precision is a critical requirement, it’s not recommended to use CURRENT_TIMESTAMP. Instead, SQL Server provides more functions that are more accurate.

2.2. Using GETDATE()

GETDATE() is another function that returns the current timestamp value, and it’s functionally equivalent to the CURRENT_TIMESTAMP function. While CURRENT_TIMESTAMP adheres to the ANSI SQL standard, GETDATE() is specific to SQL Server.

Let’s use the query using GETDATE() function:

SELECT GETDATE() AS NOW;

When executed, this returns the result in a similar way as the CURRENT_TIMESTAMP:

+--------------------------+
|           NOW            |
+--------------------------+
| 2024-06-16 05:55:17.133  |
+--------------------------+

Like the CURRENT_TIMESTAMP, the GETDATE() function uses the underlying system’s timezone but doesn’t include the offset.

2.3. Using SYSDATETIME()

The SYSDATETIME() function in SQL Server returns the current date and time with higher precision than functions like GETDATE() or CURRENT_TIMESTAMP. It retrieves the timestamp from the operating system with an accuracy of up to 100 nanoseconds. This increased precision makes SYSDATETIME() particularly useful for applications requiring fine-grained timing information.

Let’s look at the usage of this function:

SELECT SYSDATETIME() AS NOW;

When we run this query, we get the output as:

+------------------------------+
|             NOW              |
+------------------------------+
| 2024-06-16 09:11:46.4252187  |
+------------------------------+

From this, we can see that the precision is improved when using SYSDATETIME(). However, we should note that it doesn’t include the timezone offset in the result.

The SYSDATETIME() function in SQL Server returns a value of the datetime2 data type. This data type is an enhancement over the deprecated datetime type, offering greater precision and a larger date range.

2.4. Using SYSDATETIMEOFFSET()

Previously, we retrieved timestamp values without accounting for the timezone offset. However, if we need to include the offset along with the current time, we can use SYSDATETIMEOFFSET(). This function provides the same level of precision as SYSDATETIME(), but it also incorporates the timezone offset information. This capability is essential for applications that must handle and record timestamps across different time zones accurately.

Let’s look at a sample query using this function:

SELECT SYSDATETIMEOFFSET() AS NOW;

When executed, this query shows the output as:

+-------------------------------------+
|                 NOW                 |
+-------------------------------------+
| 2024-06-16 11:34:14.0875423 +02:00  |
+-------------------------------------+

We can see that the timezone offset is included in the result. This function returns a value of the datetimeoffset data type, combining high precision benefits with time zone awareness. This makes SYSDATETIMEOFFSET() particularly valuable in distributed systems and applications that operate across multiple time zones, ensuring that the exact local time and its relation to UTC are always captured.

2.5. Using SYSUTCDATETIME()

The SYSUTCDATETIME() function in SQL Server returns the current date and time in UTC with high precision. This function is similar to SYSDATETIME(), but it always returns the date and time in UTC, regardless of the server’s time zone setting. Let’s look at a sample usage:

SELECT SYSUTCDATETIME() AS NOW_UTC;

Let’s execute this query:

+------------------------------+
|           NOW_UTC            |
+------------------------------+
| 2024-06-16 09:47:23.6566475  |
+------------------------------+

The timestamp value shown in the above result represents the UTC time when this query is executed. Since it’s UTC, the result doesn’t include any offset information.

This is useful in applications requiring standardized UTC timestamps, ensuring consistency across different geographical locations and time zones.

2.6. Using GETUTCDATE()

The GETUTCDATE() function in SQL Server retrieves the current UTC date and time. Unlike GETDATE(), which returns the current local date and time based on the server’s time zone setting, GETUTCDATE() always returns the date and time in UTC format.

Let’s write a query using this function:

SELECT GETUTCDATE() AS NOW_UTC;

When we execute this query, we get the result in the UTC timezone:

+------------------------- +
|         NOW_UTC          |
+--------------------------+
| 2024-06-16 10:00:42.150  |
+--------------------------+

However, this function is not preferred as its precision is lower compared to SYSUTCDATETIME().

3. Conclusion

In this tutorial, we explored various functions in SQL Server to retrieve the current timestamp. These functions offer flexibility depending on the application’s requirements: from obtaining timestamps with or without timezone offsets to acquiring them in UTC timezone. SQL Server also provides higher precision alternatives for scenarios demanding exact timestamp accuracy. This variety of functions ensures we can choose the best way to accurately handle date and time data in our databases, meeting specific operational and analytical requirements.

As always, the sample queries used in this tutorial are available over on GitHub.