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:
Mon, 15 Aug 2016 11:36:36 UTC
Mon, 29 Aug 2016 04:37:10 GMT
Wed, 27 Jul 2016 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
Best Answer
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#:
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 :).