Skip to content
Home » Dates and Times in SQL Server: T-SQL functions to get the current date and time

Dates and Times in SQL Server: T-SQL functions to get the current date and time

watch

We have come on quite a journey so far. SQL Server and Azure SQL Database provide date and time data types to help you design the best possible database. You can read more about that here:

We then took a detour into the science of how time is measured. I strongly recommend reading these as well, before continuing on:

Functions in T-SQL

SQL Server and Azure SQL Database make use of system and user-defined functions. The first thing we see when using functions is that they make use of brackets (also known as parentheses) after the function name.

All functions (both system and user-defined) in T-SQL take zero or more parameters, which must be surrounded by opening and closing brackets.

Getting the date and time, the right and wrong way

This week we will cover four ways to get the date and time on SQL Server. Three of them are wrong, and one is right.

With these functions, no parameters will be passed in, but the brackets are required nonetheless. Under the covers, each of these functions performs a call to the operating system, which in turn returns the date and time of the computer that SQL Server (or Azure SQL Database) is running on.

Function Value Data Type Size Accuracy
GETDATE() current local date and time DATETIME 8 bytes 3 milliseconds
GETUTCDATE() current UTC date and time DATETIME 8 bytes 3 milliseconds
SYSDATETIME() current local date and time DATETIME2(7) 8 bytes 100 nanoseconds
SYSUTCDATETIME() current UTC date and time DATETIME2(7) 8 bytes 100 nanoseconds

What do we mean by local date and time?

As discussed previously, SQL Server is not time zone aware, nor does it have to be. This is because the operating system that SQL Server runs on can have multiple custom regional settings depending on which user is logged into the server.

This holds true for the SQL Server service account as well, which is just another user on the operating system. When any of these functions is called, it is asking for the date and time from the operating system.

Which function should I use?

All new development should make use of SYSUTCDATETIME(). For reasons covered in the two prior “science” posts, Daylight Saving Time and other factors can play havoc with our dates and times, especially during the changeover twice a year. With SYSUTCDATETIME(), we are at least guaranteed of being within 1 second of the Coordinated Universal Time.

The second, major advantage is the accuracy of the return value, using the same number of bytes as the old GETDATE() and GETUTCDATE(), but without that rounding to the nearest 3 milliseconds. If you are using GETDATE() or GETUTCDATE() in new development, ask yourself why.

Tune in next time for more date and time functions in T-SQL. You can also find me on Twitter at @bornsql.

Photo by Álvaro Bernal on Unsplash.
Tags:

4 thoughts on “Dates and Times in SQL Server: T-SQL functions to get the current date and time”

  1. Brian Fitzgerald

    The Microsoft manual refers to “()” as “parentheses”, not “brackets”. “Brackets” generally refers to square brackets “[]”, which Microsoft uses to delimit identifiers. In fact, parentheses and brackets can appear in the same expression, for example “[Func]()”.

    When you state “The first thing we see when using functions is that they make use of brackets after the function name.”, I’m wondering “what’s going on?.” Eventually, I get that you mean “parentheses”, but I have to keep that in mind to follow this blog series.

    1. randolph

      Thanks for taking the time to comment on this post, Brian. In South Africa where I was born and raised, we call those “square brackets”. Isn’t English wonderful? Perhaps you’ve noticed I also tend to use “s” instead of “z” in places. I hope you continue reading this series and that my terminology doesn’t throw you off too badly.

Comments are closed.