1

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
0

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 :).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.