T-SQL 101: 55 Current date and time values in SQL Server T-SQL

Finding out the current time is a really common requirement. But you also have to always consider whose concept of time you’re considering. Is the the time at the server, or the time where you are? (You might not be in the same place, or more importantly, the same timezone).

The function SYSDATETIME() returns the current system date and time right now at the server. The data type that’s returned is a datetime2.  It’s the higher precision datetime data type that was introduced in SQL Server 2008.

In much older code, you’ll see GETDATE() used. That function was a lower precision one that returned the older datetime data type. Unless there’s a real need for the older data type, I’d suggest using SYSDATETIME() now.

In the query shown above, there’s also a SYSDATETIMEOFFSET() that gives also gives you the current date and time at the server, but it returns a datetimeoffset data type i.e. it has a timezone offset as well. The timezoneoffset is based on the current setting at the server.

Once this code was executed, it returned the following output:

The server time as 9th January 2019 at 10:25 AM. The only difference between the two return values is that the second one includes a timezone offset of +11:00 or eleven hours. (That was daylight saving time in Melbourne which is otherwise 10 hours ahead of UTC).

UTC (universal time) is basically what we called Greenwich Mean Time (GMT) when I was a child. If you really want that time, rather than the local time at the server, there’s a function for that too. It’s SYSUTCDATETIME(). You can see the effect of it in the output here, where the 11 hours has been removed in the second value:

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2020-02-03