In this article, I’ll show built-in functions in SQL Server for getting the current datetime and how to get individual parts of the datetime (such as the year). Then show I’ll show how to store datetimes using the four different date/time data types (date, time, datetime2, and datetimeoffset).
Table of Contents
Getting the current datetime
SQL Server has several built-in functions for getting the current datetime. You can get the local time, UTC time, and even get the current datetime with the timezone offset.
Get the current system datetime
The following query gets the current system datetime from the computer that SQL Server is running on:
SELECT GETDATE()
Code language: SQL (Structured Query Language) (sql)This returns the following datetime:
2021-03-07 08:46:28.373Code language: plaintext (plaintext)It’s important to realize this is not the client computer’s datetime. It’s the current system datetime for the computer that SQL Server is running on.
Get the current UTC datetime
Instead of getting the current datetime of the server that SQL Server is running on, this gets the current UTC datetime:
SELECT GETUTCDATE()
Code language: SQL (Structured Query Language) (sql)This returns the following datetime:
2021-03-07 13:46:28.373Code language: plaintext (plaintext)The local datetime on the SQL Server is 08:46 and it’s running in the United States Eastern Standard timezone, which on March 8th, 2021 is 5 hours behind UTC. Hence, to get the UTC datetime, you add 5 hours to the local datetime: 08:46 + 05:00 hours = 13:46.
Get the current system datetime with timezone offset
Including the timezone offset in the datetime removes all ambiguity about what the datetime actually stands for. You don’t need to guess if the datetime is local or UTC.
To get the current datetime with the timezone offset, use the following:
SELECT SYSDATETIMEOFFSET()
Code language: SQL (Structured Query Language) (sql)This returns the following datetime:
2021-03-07 08:46:28.3741837 -05:00Code language: plaintext (plaintext)Notice the “-05:00” part of this. This is the timezone offset. It means the timezone is 5 hours behind UTC.
Including the timezone offset is the best way to store and pass around datetimes. This allows any client code, anywhere in the world, to be able to tell exactly what this datetime means.
Get the current UTC datetime with timezone offset
The problem with passing and storing UTC datetimes is that there is nothing to indicate that it’s UTC. It’s all based on convention.
To make it explicit, you can put the UTC timezone offset on the datetime, like this:
SELECT TODATETIMEOFFSET(GETUTCDATE(), '+00:00')
Code language: SQL (Structured Query Language) (sql)This returns the following datetime:
2021-03-07 13:46:28.373 +00:00Code language: plaintext (plaintext)Because the timezone offset is on the datetime, no one needs to guess if this is UTC or not.
Getting parts of the date/time
SQL Server has several built-in functions for getting individual parts of datetimes. For example, you may only want ‘2021-3-8’ and not need the time.
Note: I’m only showing GETDATE() here, but you can use these with any of the datetime getter functions (ex: GETUTCDATE()).
Get the date (without time)
The following gets just the date part of a datetime:
SELECT CONVERT(date, GETDATE())
Code language: SQL (Structured Query Language) (sql)This returns the date:
2021-03-07Code language: plaintext (plaintext)Get the time (without date)
The following gets just the time part of the datetime:
SELECT CONVERT(time, GETDATE())
Code language: SQL (Structured Query Language) (sql)This returns the time:
09:31:55.4466667Code language: plaintext (plaintext)Getting the year (or any other part of the date)
How about if you want just one part of the date, such as the year?
You can use the DATEPART() function and specify the part you want, like this:
SELECT DATEPART(YEAR, GETDATE())
Code language: SQL (Structured Query Language) (sql)This returns the year:
2021Code language: plaintext (plaintext)Getting the hour (or any other part of the time)
DATEPART() also works for getting parts of the time.
Here’s an example of getting the hour part of the datetime:
SELECT DATEPART(HOUR, GETDATE())
Code language: SQL (Structured Query Language) (sql)This returns the hour:
9Code language: plaintext (plaintext)Storing datetime
There are four data types to know about when dealing with datetimes: date, time, datetime2, and datetimeoffset.
Create a table with datetime columns
The following statement creates a table containing columns with types: date, time, datetime2, and datetimeoffset:
CREATE TABLE [dbo].[Orders](
[OrderId] [int] NULL,
[OrderDate] [date] NULL,
[OrderTime] [time](7) NULL,
[OrderDateTimeOffset] [datetimeoffset](7) NULL,
[OrderDateTimeUtc] [datetime2](7) NULL
) ON [PRIMARY]
Code language: SQL (Structured Query Language) (sql)Note: This example is creating the table with a SQL statement. In an actual project, I’d suggest creating the database and tables with EF Core.
Insert datetime data
Now insert records with datetime values into the table:
DECLARE @now datetime
DECLARE @nowUtc datetime
DECLARE @nowWithOffset datetimeoffset
SELECT @now = GETDATE(), @nowUtc = GETUTCDATE(), @nowWithOffset = SYSDATETIMEOFFSET()
INSERT INTO [dbo].[Orders]
([OrderId]
,[OrderDate]
,[OrderTime]
,[OrderDateTimeOffset]
,[OrderDateTimeUtc])
VALUES
(1
,@now
,@now
,@nowWithOffset
,@nowUtc)
Code language: SQL (Structured Query Language) (sql)This inserted the following data:
| OrderId | OrderDate | OrderTime | OrderDateTimeOffset | OrderDateTimeUtc |
| 1 | 2021-03-07 | 09:59:49.2633333 | 2021-03-07 09:59:49.2664787 -05:00 | 2021-03-07 14:59:49.2633333 |
Notice that it’s passing a datetime variable into the date (OrderDate) and time (OrderTime) columns and it’s only saving the date or time part of it. This is nice, because it means the client doesn’t need to deal with stripping out the date/time.
Why to use datetimeoffset
In general, datetime data should be stored and passed around in the ISO-8601 format with the timezone offset. This is what the datetimeoffset data type provides. When you use this standard format, there’s no ambiguity about what the datetime stands for.
Embedding the timezone offset with your datetimes is especially important when you consider daylight savings time shifts, and the fact that timezone rules are based on political rules. This means they can change at any time.
If you can’t use a datetimeoffset column for some reason, then storing UTC datetimes is the second best choice. Notice that the table above has a column called OrderDateTimeUtc with data type datetime2. This is the key problem with using UTC – there’s no way to enforce saving UTC datetimes in this column. It’s based on convention (you have “UTC” in the column name, so you hope the client always passes in UTC datetimes).
datetimeoffset defaults to timezone offset +00:00
If the datetime inserted into the datetimeoffset column doesn’t have the timezone offset specified, then it defaults to +00:00.
For example, consider the following insert statement:
INSERT INTO [dbo].[Orders]
([OrderId]
,[OrderDateTimeOffset])
VALUES
(1, GETDATE())
Code language: SQL (Structured Query Language) (sql)GETDATE() returns “2021-03-07 10:02:53.2700000”, which doesn’t have an offset. When this is inserted into the table, it defaults to having the +00:00 offset, like this:
2021-03-07 10:02:53.2700000 +00:00Code language: plaintext (plaintext)I’m running SQL Server on a server in a timezone with offset -05:00. So the default timezone offset of +00:00 is wrong.
If you’re using a datetimeoffset column, always make sure to explicitly specify the timezone offset.
Inserting a UTC datetime into a datetimeoffset column
The default offset of datetimeoffset columns is +00:00, which is the UTC timezone offset.
If you’re using GETUTCDATE() to get the UTC datetime, it won’t have the timezone offset appended to it. But if you’re inserting into a datetimeoffset column, then it will put the UTC timezone offset for you.
To show this, take a look at the following insert statement:
INSERT INTO [dbo].[Orders]
([OrderId]
,[OrderDateTimeOffset])
VALUES
(1, GETUTCDATE())
Code language: SQL (Structured Query Language) (sql)GETUTCDATE() returns “2021-03-07 15:11:14.8300000”. Notice, there’s no timezone offset. When this is inserted into the table, it looks like this:
2021-03-07 15:11:14.8300000 +00:00Code language: plaintext (plaintext)It automatically appended the +00:00 timezone offset. This default behavior is only correct if you’re using UTC datetimes. Otherwise you should always explicitly specify the timezone offset and not let it put the default.