This post continues our look at date and time data types in SQL Server.
SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the DATETIME and SMALLDATETIME types that we looked at previously.
This week, we look at the last new data type, DATETIMEOFFSET. If you’d like to look at the previous posts, you can visit them from here:
DATETIMEOFFSET works the same way as the DATETIME2 data type, except that it is also time zone aware. It is formatted as 'YYYY-MM-DD HH:mm:ss[.nnnnnnn][{+|-}hh:mm]'.
Got all that? YYYY represents a four-digit year, MM is a two-digit month between 1 and 12, DD is a two-digit day between 1 and 31 depending on the month, HH represents a two-digit hour between 0 and 23, mm is the minutes between 0 and 59, while ss is the number of seconds between 0 and 59. Once again, n represents between zero and seven decimal places in a fraction of a second.
The main difference from DATETIME2 is the time zone offset at the end, which is the number of hours and minutes as an offset from UTC time. Valid values range from -14:00 to +14:00. The time zone information requires an additional two bytes of storage over DATETIME2, for a range of 8 to 10 bytes, depending on the scale.
Let’s use an example. Today is March 28th, 2018, and the time is roughly 9:03am in my time zone.
This is how we would represent this in various date and time data types. For my local time, I’m using Calgary’s offset, which is Mountain Daylight Time (MDT), or UTC-0700 (seven hours behind Universal Coordinated Time).
| Data Type | Output | Storage |
DATETIME: |
'2018-03-28 09:03:44.133' |
8 bytes |
SMALLDATETIME: |
'2018-03-28 09:04' |
4 bytes |
DATE: |
'2018-03-28' |
3 bytes |
DATETIME2(0): |
'2018-03-28 09:03:44' |
6 bytes |
DATETIME2(1): |
'2018-03-28 09:03:44.1' |
6 bytes |
DATETIME2(2): |
'2018-03-28 09:03:44.13' |
6 bytes |
DATETIME2(3): |
'2018-03-28 09:03:44.133' |
7 bytes |
DATETIME2(4): |
'2018-03-28 09:03:44.1327' |
7 bytes |
DATETIME2(5): |
'2018-03-28 09:03:44.13266' |
8 bytes |
DATETIME2(6): |
'2018-03-28 09:03:44.132657' |
8 bytes |
DATETIME2(7): |
'2018-03-28 09:03:44.1326566' |
8 bytes |
DATETIMEOFFSET(0): |
'2018-03-28 09:03:44 -07:00' |
8 bytes |
DATETIMEOFFSET(1): |
'2018-03-28 09:03:44.1 -07:00' |
8 bytes |
DATETIMEOFFSET(2): |
'2018-03-28 09:03:44.13 -07:00' |
8 bytes |
DATETIMEOFFSET(3): |
'2018-03-28 09:03:44.133 -07:00' |
9 bytes |
DATETIMEOFFSET(4): |
'2018-03-28 09:03:44.1327 -07:00' |
9 bytes |
DATETIMEOFFSET(5): |
'2018-03-28 09:03:44.13266 -07:00' |
10 bytes |
DATETIMEOFFSET(6): |
'2018-03-28 09:03:44.132657 -07:00' |
10 bytes |
DATETIMEOFFSET(7): |
'2018-03-28 09:03:44.1326566 -07:00' |
10 bytes |
As with TIME and DATETIME2, if we leave out the scale, DATETIMEOFFSET will default to DATETIMEOFFSET(7), providing seven decimal places for fractions of a second, consuming 10 bytes.
We can convert to and from these different data types, but information can be lost in this conversion, especially with decimal accuracy.
Now that we have a clearer understanding of these data types, the next few posts will cover the various operations and functions that are possible with dates and times in SQL Server and Azure SQL Database.
Feel free to share your Daylight Saving horror stories with me on Twitter at @bornsql, or in the comments.
Photo by Adolfo Félix on Unsplash.
In the 4th – 11th rows of your table, do you mean DATETIME2(x), not just DATETIME(x)?
Overall, helpful series of posts on the date/time types. Thanks!
Good catch. Thank you for noticing that!
I always hate it when people claim that DATETIMEOFFSET is “time zone aware”. It isn’t and doesn’t add up to even a half-measure to fix time zone problems. It may add some help converting times in some very special circumstances, but all it does is record the DST _offset_ for a single instant in a _specific_ time zone. It knows nothing about the actual time zone from which it originated, nor about _any_ time zone for that matter.
To illustrate the problem, take a DATETIMEOFFSET value. Try to decipher what time zone it came from. There may be a few time zones where you can figure out the time zone from the datetime and its offset but in general this is impossible. So: no, DATETIMEOFFSET is not time zone aware.
Please provide a proper explanation that dealing with time zones is a much more complicated problem that DATETIMEOFFSET simply does not address!
Ed, I can tell you’re passionate about this subject, and I appreciate your feedback. Please note that your concerns will be addressed in more in-depth posts coming later in this series.
At the risk of making myself like a super geek, I will confess that I actually stayed up and watched my “atomic clock” (driven off of the radio time signal) rollover for the leap second. Hopefully were going to get rid of leap second sometime soon. Of course I’m still waiting for the US to get on the metric system… Good article
Comments are closed.