Explore the DateTime and DateTimeOffset data types in SQL Server 2016 🦸 Learn how to handle time zone offsets and store date and time values accurately
SQL Server DATETIMEOFFSET Data Type
The DATETIMEOFFSET data type in SQL Server allows for storing date and time values with time zone awareness. This data type combines features of both the DATETIME2 and TIME data types, while also adding the ability to store a time zone offset.
What is a DateTimeOffset?
A DateTimeOffset is a data structure that contains a date and time value along with an offset from Coordinated Universal Time (UTC). The offset specifies how much the DateTimeOffset differs from UTC time. This allows DateTimeOffsets to represent local date and time values in different time zones.
DateTimeOffset in SQL Server
The DATETIMEOFFSET data type was introduced in SQL Server 2008. It has several advantages over the DATETIME data type:
- Stores date and time values with up to 7 decimal places for the seconds fraction (DATETIME only has 3)
- Includes a time zone offset from UTC (-14:00 to +14:00)
- Time zone offset is preserved when storing data, allowing conversion between time zones
- Supports a wider date range (January 1, 1 CE to December 31, 9999 CE)
Syntax and Properties
The basic syntax for declaring a DATETIMEOFFSET is:
DATETIMEOFFSET [ (precision) ]Where precision is the number of decimal places for the seconds fraction, from 0 to 7.
The default properties when not specifying precision are:
- Date range: January 1, 1 CE to December 31, 9999 CE
- Time range: 00:00:00 to 23:59:59.9999999
- Precision: 7 decimal places for seconds fraction
- Time zone offset range: -14:00 to +14:00
- Storage size: 10 bytes
DATETIMEOFFSET Examples
To declare a DATETIMEOFFSET variable with 5 decimal places for seconds:
DECLARE @datetimeoffset DATETIMEOFFSET(5)To create a database table column to store DATETIMEOFFSET values:
CREATE TABLE Sales ( SaleDate DATETIMEOFFSET(3) )When inserting DATETIMEOFFSET values, the format is:
YYYY-MM-DD hh:mm:ss.nnnnnnn [{+|-}hh:mm]For example:
INSERT INTO Sales VALUES ('2020-01-30 10:15:23.34567 -05:00')The time zone offset is specified after the seconds fraction in +/-hh:mm format.
Using DATETIMEOFFSET
The DATETIMEOFFSET data type is useful when you need to store date and time values from multiple time zones in the same data source. For example, a sales system that tracks orders globally could store local order dates and times as DATETIMEOFFSET. This retains the original time zone information.
When querying DATETIMEOFFSET columns, you can convert values to a specified time zone using the AT TIME ZONE function introduced in SQL Server 2016:
SELECT OrderDate AT TIME ZONE 'Eastern Standard Time' FROM SalesThis allows standardized reporting in a single time zone.
Converting to DATETIMEOFFSET
You can convert DATE, DATETIME2, and DATETIME values to DATETIMEOFFSET using the CAST or CONVERT functions:
SELECT CAST(OrderDate AS DATETIMEOFFSET) AS DateTimeOffset, CONVERT(DATETIMEOFFSET, OrderDate) AS DateTimeOffset FROM SalesWhen converting STRING values, SQL Server will parse dates and times in ISO-8601 formats. The time zone offset is optional.
You can also convert DATETIMEOFFSET back to DATE, DATETIME2, DATETIME or STRING types. The time zone offset will be removed from the result.
How SQL Server Stores DATETIMEOFFSET
Under the hood, SQL Server stores the date and time parts of a DATETIMEOFFSET value in UTC. Only the fractional seconds precision and time zone offset are actually persisted in the database. The time zone offset uses two bytes formatted as +/-nnnn to support offsets from -14:00 to +14:00.
During queries, the UTC date and time is combined with the stored time zone offset to reconstruct the original local date and time. This allows efficient storage and computation while still preserving the offset information.
Key Benefits
The DATETIMEOFFSET data type provides key benefits for working with data across time zones:
Datetime
The datetime data type in SQL Server stores date and time values without any time zone awareness. It has a range from January 1, 1753 to December 31, 9999. The time component has accuracy up to 1/300th of a second. Datetime does not store any information about time zones. When datetime values from different time zones are stored together, this can lead to inconsistent sorting and calculations.
Time Zone Offset
The time zone offset component of a datetimeoffset value specifies the difference between the local time and UTC. It is formatted as [+|-]hh:mm. For example, Pacific Standard Time has an offset of -08:00 from UTC, while India Standard Time is +05:30 ahead of UTC. The offset range supported in datetimeoffset is from -14:00 to +14:00. Negative offsets are stored by subtracting the offset amount from the maximum value of 65,536. During daylight saving time transitions, the offset typically changes by 1 hour as clocks are adjusted.
Date and Time Types
In addition to datetimeoffset, SQL Server supports a number of other date and time types including date, time, datetime2, datetime, and smalldatetime. Each serves a different purpose. Date stores just the date component. Time stores the time of day with scale up to 7 decimal places for seconds. Datetime2 also has precision up to 7 decimals for seconds, but no time zone awareness. Datetime and smalldatetime have less precision and range than datetimeoffset.
String Literal
Date and time values can be converted to and from string literals in SQL Server. When inserting into a datetimeoffset column from a string, SQL Server supports ISO 8601 formats. After converting a datetimeoffset to a string, the default format will be YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. To control string formatting, you can use functions like FORMAT() or CAST/CONVERT with a specified style code.
Converting Date and Time Values
It’s common to convert between the various date and time types in SQL Server. When converting datetimeoffset to date or time, the date and time components are copied, while the timezone offset is truncated. Converting smalldatetime to datetimeoffset will copy the date and hours, rounding minutes and truncating seconds. Converting datetime to datetimeoffset will produce a UTC offset of +00:00. In some cases, such as datetime to datetimeoffset, default values may need to be supplied for the missing components.
Time Zones
Since datetimeoffset stores UTC values internally, it supports easy conversion between different time zones. By using AT TIME ZONE, you can convert a datetimeoffset value to any desired target time zone. SQL Server will adjust the UTC value along with the offset to match the target zone. This allows flexible time zone conversion and standardized reporting without losing data fidelity.
Default Format
When converting datetimeoffset to a string without specifying a format, SQL Server uses a default format of YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. This ISO8601-like format includes all components of a datetimeoffset value – the date, time, fractional seconds, and UTC offset. To convert a string back to datetimeoffset, SQL Server can parse strings in this and other ISO-compliant formats.
Time Zone Conversion
AT TIME ZONE is an elegant way to convert between time zones. Behind the scenes, SQL Server stores datetimeoffset values in UTC. The UTC date/time and offset are adjusted together to match the desired target time zone. Daylight saving time transitions are handled automatically based on the target zone. AT TIME ZONE helps simplify reporting and analysis across time zones within the database.
Stored in the Database
A key thing to understand about datetimeoffset is its storage in SQL Server. Only the UTC date/time value and the timezone offset are actually persisted. The UTC components are stored like a datetime2 value, while the timezone uses two bytes to represent the offset in +/-nnnn format. When querying the data, SQL Server uses these parts to reconstruct the original local datetime and offset.
Value Already
When converting datetimeoffset values with AT TIME ZONE, be aware that the target time zone may have already transitioned to/from daylight saving time even if the source value has not. SQL Server will adjust the UTC value and offset to match the current state of the target zone. This prevents invalid local times during ambiguous periods when clocks are adjusted forwards or backwards.
Obtain Local Time
The datetimeoffset data type provides an efficient way to store local date and time values from multiple time zones while retaining the original timezone information. By preserving the offset, datetimeoffset allows converting UTC values back to local time as needed for retrieval and display. The offset also enables flexible conversion between time zones using AT TIME ZONE when required.
Seconds Value
The precision supported for the fractional seconds component is a major distinction between datetimeoffset and earlier date/time types like datetime. With up to 7 decimals, datetimeoffset allows accurately tracking fractions of a second which is important in many domains. Applications like financial trading, scientific data recording, and vehicle telemetry require precision beyond whole seconds.
For applications that deal with diverse time zones, DATETIMEOFFSET is a more capable date time data type than DATETIME or DATETIME2 in SQL Server.
