Convert rfc822 to either a GMT DATETIME or Unix timestamp in SQL Server

Question

I am trying to find a way to convert an rfc822 string date (with timezone) to either a GMT value of data type DATETIME or a unix timestamp of data type INT. For example:

2016年8月15日 11:36:36 UTC
2016年8月29日 04:37:10 GMT
2016年7月27日 14:41:05 UTC

I believe the "UTC" and "GMT" portions actually mean the same thing, and for my particular use case, I believe those are the only two timezone values I will see.

I am working in a SQL Server 2008r2 environment.

The only thing I am finding to do this is the function below, but I want to avoid that for a few reasons:

• It uses hardcoded offsets
• It needs registry read access (which I don't think I will have with my hosting provider)
• I am hoping not to have a dependency on a user defined function

Are there any easier simpler ways of accomplishing this conversion? Even better would be something I can use natively in T-SQL instead of creating a function dependency.

CREATE FUNCTION udf_ConvertTime (
 @TimeToConvert varchar(80),
 @TimeZoneTo varchar(8)
)
RETURNS DateTime
AS
BEGIN
 DECLARE @dtOutput datetime,
 @nAdjust smallint,
 @hh smallint,
 @Loc smallint,
 @FromDate datetime,
 @mm smallint,
 @Ndx tinyint,
 @TimeZoneFrom varchar(80),
 @WkTime varchar(80)
 SET @TimeZoneTo = ISNULL(@TimeZoneTo, 'LOCAL')
 /* ------------------------------------------------------------------------ */
 /* Important: If you want to convert to your local time, the following is */
 /* necessary to handle daylight savings time. Your SQLServer installation */
 /* must allow this function to execute xp_regread. */
 /* ------------------------------------------------------------------------ */
 SET @Loc = CONVERT(smallint, DATEDIFF(hh, GETUTCDATE(), GETDATE()) * 60)
 IF @TimeZoneTo = 'LOCAL'
 BEGIN
 DECLARE @root VARCHAR(32),
 @key VARCHAR(128),
 @StandardBias VARBINARY(8),
 @DaylightBias VARBINARY(8)
 SET @root = 'HKEY_LOCAL_MACHINE'
 SET @key = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
 EXEC master..xp_regread @root, @key, 'StandardBias', @StandardBias OUTPUT
 EXEC master..xp_regread @root, @key, 'DaylightBias', @DaylightBias OUTPUT
 IF @StandardBias <> @DayLightBias
 SET @Loc = @Loc - 60
 END
 /* ------------------------------------------------------------------------ */
 /* Build a temporary table of timezone conversions. */
 /* ------------------------------------------------------------------------ */
 DECLARE @Temp TABLE (
 TimeZone varchar(8),
 nOffset smallint )
 INSERT INTO @Temp
 SELECT 'A', 60 UNION ALL
 SELECT 'ACDT', 630 UNION ALL
 SELECT 'ACST', 570 UNION ALL
 SELECT 'ADT', -180 UNION ALL
 SELECT 'AEDT', 660 UNION ALL
 SELECT 'AEST', 600 UNION ALL
 SELECT 'AKDT',-480 UNION ALL
 SELECT 'AKST',-540 UNION ALL
 SELECT 'AST', -240 UNION ALL
 SELECT 'AWDT', 540 UNION ALL
 SELECT 'AWST', 480 UNION ALL
 SELECT 'B', 120 UNION ALL
 SELECT 'BST', 60 UNION ALL
 SELECT 'C', 180 UNION ALL
 SELECT 'CDT', -300 UNION ALL
 SELECT 'CEDT', 120 UNION ALL
 SELECT 'CEST', 120 UNION ALL
 SELECT 'CET', 60 UNION ALL
 SELECT 'CST', -360 UNION ALL
 SELECT 'CXT', 420 UNION ALL
 SELECT 'D', 240 UNION ALL
 SELECT 'E', 300 UNION ALL
 SELECT 'EDT', -240 UNION ALL
 SELECT 'EEDT', 180 UNION ALL
 SELECT 'EEST', 180 UNION ALL
 SELECT 'EET', 120 UNION ALL
 SELECT 'EST', -300 UNION ALL
 SELECT 'F', 360 UNION ALL
 SELECT 'G', 420 UNION ALL
 SELECT 'GMT', 0 UNION ALL
 SELECT 'H', 480 UNION ALL
 SELECT 'HAA', -180 UNION ALL
 SELECT 'HAC', -300 UNION ALL
 SELECT 'HADT',-540 UNION ALL
 SELECT 'HAE', -240 UNION ALL
 SELECT 'HAP', -420 UNION ALL
 SELECT 'HAR', -360 UNION ALL
 SELECT 'HAST',-600 UNION ALL
 SELECT 'HAT', -150 UNION ALL
 SELECT 'HAY', -480 UNION ALL
 SELECT 'HNA', -240 UNION ALL
 SELECT 'HNC', -360 UNION ALL
 SELECT 'HNE', -300 UNION ALL
 SELECT 'HNP', -480 UNION ALL
 SELECT 'HNR', -420 UNION ALL
 SELECT 'HNT', -210 UNION ALL
 SELECT 'HNY', -540 UNION ALL
 SELECT 'I', 540 UNION ALL
 SELECT 'IST', 60 UNION ALL
 SELECT 'K', 600 UNION ALL
 SELECT 'L', 660 UNION ALL
 SELECT 'LOC', @Loc UNION ALL
 SELECT 'LOCAL',@Loc UNION ALL
 SELECT 'M', 720 UNION ALL
 SELECT 'MDT', -360 UNION ALL
 SELECT 'MESZ', 120 UNION ALL
 SELECT 'MEZ', 60 UNION ALL
 SELECT 'MST', -420 UNION ALL
 SELECT 'N', -60 UNION ALL
 SELECT 'NDT', -150 UNION ALL
 SELECT 'NFT', 690 UNION ALL
 SELECT 'NST', -210 UNION ALL
 SELECT 'O', -120 UNION ALL
 SELECT 'P', -180 UNION ALL
 SELECT 'PDT', -420 UNION ALL
 SELECT 'PST', -480 UNION ALL
 SELECT 'Q', -240 UNION ALL
 SELECT 'R', -300 UNION ALL
 SELECT 'S', -360 UNION ALL
 SELECT 'T', -420 UNION ALL
 SELECT 'U', -480 UNION ALL
 SELECT 'UTC', 0 UNION ALL
 SELECT 'V', -540 UNION ALL
 SELECT 'W', -600 UNION ALL
 SELECT 'WEDT', 60 UNION ALL
 SELECT 'WEST', 60 UNION ALL
 SELECT 'WET', 0 UNION ALL
 SELECT 'WST', 540 UNION ALL
 SELECT 'WST', 480 UNION ALL
 SELECT 'X', -660 UNION ALL
 SELECT 'Y', -720 UNION ALL
 SELECT 'Z', 0
 /* ------------------------------------------------------------------------ */
 /* If timezone is embedded within @TimeToConvert, separate it out. If we */
 /* can at all convert this date with SQL, do it. */
 /* ------------------------------------------------------------------------ */
 SET @Ndx = CHARINDEX(' ', REVERSE(@TimeToConvert))
 IF @Ndx > 0
 BEGIN
 SET @TimeZoneFrom = RIGHT(@TimeToConvert, @Ndx - 1)
 IF 'TRUE' = CASE
 WHEN @TimeZoneFrom LIKE '[0-9][0-9][0-9][0-9]' THEN 'TRUE'
 WHEN @TimeZoneFrom LIKE '[+][0-9][0-9][0-9][0-9]' THEN 'TRUE'
 WHEN @TimeZoneFrom LIKE '[-][0-9][0-9][0-9][0-9]' THEN 'TRUE'
 ELSE 'FALSE'
 END
 BEGIN -- This has already converted offset hhmm
 SET @hh = CONVERT(smallint, LEFT(@TimeZoneFrom, LEN(@TimeZoneFrom) - 2))
 SET @mm = CONVERT(smallint, RIGHT(@TimeZoneFrom, 2))
 SET @nAdjust = (@hh * 60) + @mm
 SET @TimeToConvert = LEFT(@TimeToConvert, LEN(@TimeToConvert) - @Ndx)
 END
 ELSE
 IF EXISTS (SELECT 1 FROM @Temp
 WHERE TimeZone = @TimeZoneFrom)
 SET @TimeToConvert = LEFT(@TimeToConvert, LEN(@TimeToConvert) - @Ndx)
 ELSE
 SET @TimeZoneFrom = NULL
 END
 IF ISDATE(@TimeToConvert) = 1
 SET @FromDate = CONVERT(datetime, @TimeToConvert)
 SET @TimeZoneFrom = ISNULL(@TimeZoneFrom, 'LOCAL')
 /* ------------------------------------------------------------------------ */
 /* We are providing a varchar(80) date field to facilitate RFC822 dates. */
 /* ------------------------------------------------------------------------ */
 IF @FromDate IS NULL
 BEGIN
 SET @Ndx = 1
 SET @WkTime = REPLACE(@TimeToConvert, ',', '')
 SET @WkTime = REVERSE(
 SUBSTRING(
 REVERSE(
 SUBSTRING(@WkTime, 5, LEN(@WkTime))
 ), @Ndx, LEN(@WkTime)))
 IF CHARINDEX(' ', @WkTime) = 4 AND
 CHARINDEX(' ', @WkTime, 5) = 7 AND
 CHARINDEX(':', @WkTime, 8) = 10 AND
 CHARINDEX(':', @WkTime, 11) = 13 -- Means we have no year
 SET @WkTime = LEFT(@WkTime, 7) + CONVERT(varchar(5), YEAR(GETDATE())) + SUBSTRING(@WkTime, 7, 40)
 IF ISDATE(@WkTime) = 1
 SET @FromDate = CONVERT(datetime, @WkTime)
 END
 IF @FromDate IS NULL
 RETURN @FromDate
 /* ------------------------------------------------------------------------ */
 /* If the from and to are the same, we need go no further. */
 /* ------------------------------------------------------------------------ */
 IF ISNULL(@TimeZoneFrom, '') IN (ISNULL(@TimeZoneTo, ''), ISNULL(@TimeZoneTo, 'LOCAL'))
 RETURN @FromDate
 /* ------------------------------------------------------------------------ */
 /* Return the difference between the from/to timezones. */
 /* ------------------------------------------------------------------------ */
 IF @nAdjust IS NULL
 BEGIN
 SELECT @nAdjust = nOffset
 FROM @Temp
 WHERE timeZone = @TimeZoneFrom
 IF EXISTS (SELECT 1 FROM @Temp
 WHERE timeZone = @TimeZoneTo)
 SELECT @nAdjust = nOffset - @nAdjust
 FROM @Temp
 WHERE timeZone = @TimeZoneTo
 END
 SET @dtOutput = DATEADD(n, ISNULL(@nAdjust, 0), @FromDate)
 RETURN @dtOutput
END

• Aaron Bertrand: A problematic assumption in your function is that today's offset from UTC is the same as the offset is/was on whatever datetime value has been passed in. If you run the function today but the datetime was from Christmas, you're going to have incorrect results. I think you should separate the problem of (a) converting a string in a non-standard format to a datetime value from the problem of (b) converting that datetime value to the right time zone-specific time.
• Aaron Bertrand: This series might be useful: mssqltips.com/sqlservertip/3173/… mssqltips.com/sqlservertip/3174/… mssqltips.com/sqlservertip/3175/…

Answer 1

I am not sure if SQLCLR is an option for you given what you mentioned about using a hosted system, but if you can use SQLCLR, then this is rather straight-forward in .NET / C#:

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static DateTime? DateTimeFromRFC822([SqlFacet(MaxSize = 50)] SqlString StringValue)
{
 DateTime __ReturnVal = new DateTime();
 if (DateTime.TryParse(StringValue.Value, out __ReturnVal);
 {
 return (DateTime?)__ReturnVal;
 }
 return (DateTime?)null;
}

And it will take into account historical timezone / DST info.

As a side note: this functionality, along with being able to specify the culture, is in the SQL# SQLCLR library (that I am the author of) as of version 4.0. It is called String_TryParseToDateTime and is available in the Free version :).