Time zones and the DATETIMEOFFSET data type in SQL

-- Database by Doug
-- Douglas Kline
-- 10/18/2018
-- Time Zones and DateTimeOffset

-- how to use time zones and convert from one to another

-- first, how to think about date/time/datetime in SQL
-- suppose I do this:

SELECT GETDATE() AS [now, somewhere]

-- the data type that is returned is a datetime
-- I can show this:
SELECT SQL_VARIANT_PROPERTY(GETDATE(), 'BaseType')

-- think of the datetime data type as a 
-- multi-part object that holds each 
-- part as a separate value

-- for example:
SELECT   GETDATE(),
         DATEPART(YEAR, GETDATE())        AS [year],
         DATEPART(MONTH, GETDATE())       AS [month],
         DATEPART(DAY, GETDATE())         AS [day],
         DATEPART(HOUR, GETDATE())        AS [hour],
         DATEPART(SECOND, GETDATE())      AS [second],
         DATEPART(MILLISECOND, GETDATE()) AS [millisecond]

-- note that all the parts are whole numbers
-- DATEPART() always returns whole numbers - no decimals

-- and when we manipulate datetimes, we use whole numbers:
SELECT   GETDATE()                     AS [now],
         DATEADD(DAY, 2, GETDATE())    AS [2 days from now],
         DATEADD(DAY, 2.5, GETDATE())  AS [2.5 days from now?],
         DATEADD(HOUR, 12, 
            DATEADD(DAY, 2, GETDATE()))AS [2.5 days from now]

-- look at that last entry again
            
-- notice that 2.5 was truncated to an integer
-- there is no such thing as a fractional part of a datetime

-- regardless of how SQL Server stores the date/time/datetime
-- under the covers
-- it treats each part as a separate integer value

-- so what about time zones?
-- since datetime (and datetime2) doesn't have a time zone part
-- we need a new data type: datetimeoffset

-- you can the server datetime with time zone like this:
SELECT SYSDATETIMEOFFSET(), 
       SQL_VARIANT_PROPERTY(SYSDATETIMEOFFSET(), 'BaseType')

-- my current database server happens to be
-- in the eastern time zone of the US, which is -04:00 UTC

-- note that the datetimeoffset data type
-- has everything that datetime does
-- but additionally, it has another part, the time zone

-- so how would I show the actual east coast time?

-- you might think that the function TODATETIMEOFFSET
-- would do that for you, but all it really does is
-- add the time zone to a datetime

SELECT TODATETIMEOFFSET(GETDATE(),'-04:00') AS [time on east coast?],
       SYSDATETIMEOFFSET()                  AS [time on east coast],
       CAST(GETDATE() AS DATETIMEOFFSET)    AS [time on east coast?] 

-- note that the hours are the same
-- it didn't really move time zones for you
-- it just added the time zone information you gave it

-- to actually adjust the hours, you need to
-- actually add/subtract the hours

SELECT   GETDATE()                        AS [implied time zone],
         TODATETIMEOFFSET
         (
            GETDATE(), 
            '-00:00'
         )                                AS [explicit UTC +00:00 time zone], 
         DATEADD(hour, -4, GETDATE())     AS [east coast time, implied time zone],
         TODATETIMEOFFSET
         (
            DATEADD(hour, -4, GETDATE()),
            '-04:00'
         )                                AS [east coast with time zone]

SELECT GETDATE(), SYSDATETIMEOFFSET()

-- in summary
-- DATETIMEOFFSET is a DATETIME with additional information: the time zone
-- TODATETIMEOFFSET converts from DATETIME to DATETIMEOFFSET
-- but doesn't add or subtract time
-- to take a datetime from one time zone, and show it in another
-- timezone, you have to add/subtract the hours yourself
-- and set the correct time zone in a DATETIMEOFFSET

-- Database by Doug
-- Douglas Kline
-- 10/18/2018
-- Time Zones and DateTimeOffset