I recently had a project where we were receiving a file with a date stamp in the file name, in the format yyyyMMddHHmm, so the date plus the hour and minute. The file was produced on the West Coast in Pacific time (I’m in the Eastern time zone). We want to store that date/time, where we store time as UTC.
Because of daylight saving time (DST), we can’t just add a set number of hours to the Pacific time to get UTC, unless we can determine if the input time in in standard time or DST. I’ve worked at places where we maintained a table with all of the start and end dates for DST, and would look up each time in order to convert the time.
Luckily, SQL Server has support for standard time and DST to determine which is appropriate for a specific time.
Here is the SQL code I ended up using.
DECLARE @input char(12) = '201808311200'; DECLARE @output datetimeoffset; DECLARE @year int = CAST(SUBSTRING(@input, 1, 4) AS int); DECLARE @month tinyint = CAST(SUBSTRING(@input, 5, 2) AS tinyint); DECLARE @day tinyint = CAST(SUBSTRING(@input, 7, 2) AS tinyint); DECLARE @hour tinyint = CAST(SUBSTRING(@input, 9, 2) AS tinyint); DECLARE @minute tinyint = CAST(SUBSTRING(@input, 11, 2) AS tinyint); SET @output = (DATETIMEFROMPARTS(@year, @month, @day, @hour, @minute, 0, 0)) AT TIME ZONE 'Pacific Standard Time'; SET @output = @output AT TIME ZONE 'UTC'; SELECT @output;
First, I parsed the input string into the separate date/time parts. Alternatively, We could have added formatting to the input string (Like 2018-08-31 12:00:00) and converted that directly to a datetime.
If we use the datetimeoffset data type, we can use the AT TIME ZONE statement to designate what time zone our time is in. In this case (with Pacific time), we’ll end up with an offset of -7:00. We’ll use ‘Pacific Standard Time’ even though DST is in effect, since the point of the functionality is for us to not have to make the DST determination.
You can query the sys.time_zone_info system table to see a list of the time zones that can be used with this statement.
We’ll make one last conversion to UTC to end up with the datetime that we’ll store.
While on the subject of time zones, here are a few things on UTC. UTC stands for Coordinated Universal Time, the acronym is a compromise between the English and French phrases. (Wikipedia).
People use UTC and GMT(Greenwich Mean Time) as synonyms, and the time for each in within fractions of a second, but there is a difference between the two. GMT is a time zone, and is based on astronomical observations. UTC is a time standard (not a time zone) as in based on atomic clocks.