<

Learn how to a build a cloud-first strategyRegister Now

x

SQL Server - Converting RFC822 Dates Into Any Timezone

Published on
5,455 Points
2,355 Views
1 Endorsement
Last Modified:
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
 

The problem is that RFC822 date formats do not always fit within SQL Server's valid date formats. The CONVERT/CAST functions within SQL Server will only convert a limited variety of date formats. When trying to convert RFC822 date formats, some of the RFC822 accepted formats will result in the well-known T-SQL error:
 
Msg 241, Level 16, State 1, Line 1 
Conversion failed when converting date and/or time from character string.

According to the RFC822: Standard for ARPA Internet Text Messages, as long as a date adheres to the constraints in section 5 of the RFC822 protocols, it is a valid RFC822 date. Many RFC822 Dates fail to convert into a T-SQL datetime format.

For instance, the following is a valid RFC822 date:
 
Wed, 27 Sep 2006 21:49:19 GMT

This format (and a number of other RFC822 formats) will not convert to a datetime type. In addition SQL Server does not inherently recognize nor convert time zone codes. (See footnote 1 at the end of this article for a list of valid RFC822 date formats.)

I have written a function called udf_ConvertTime. This function handles most RFC822 dates, as well as a few other common styles of date formatting. It also handles daylight savings time. It expects you to pass the following parameters when calling it (TZ = time zone):
 
udf_ConvertTime('Your_RFC822_DateString TZ', 'Target TZ')

An example of how to call this function would be:
 
SELECT dbo.udf_ConvertTime('Wed, 16 Dec 2009 15:18:11 PDT', 'LOCAL')

Values for the second parameter (target time zone) can be:
 
  • 'LOCAL' or NULL (converts to the time zone your computer is on)
  • Any valid time zone abbreviation
  • +/-HHMM (must start with the + or -, and must be four digits in the format HHMM)

If you pass NULL or 'LOCAL', please note that your SQL Server installation must allow the function xp_regread to be executed (which gets your local time zone from the registry).

Function udf_ConvertTime is below:
 
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

Open in new window

This function will strip off any time zone at the end of the string. It will then verify if this date can already be converted within T-SQL's CONVERT function. If not, it will parse the string, recognizing various RFC822 acceptable formats, until it can recognize it as a valid datetime string and convert it.

The script below shows how udf_ConvertTime works. The dates are all acceptable dates according to RFC822, including the first three records which have time zones appended to them:
 
DECLARE @TestTable TABLE (
    Seq                 int NOT NULL IDENTITY(1, 1),
    RFC822Date          varchar(32),
    MyConvertedDate     datetime,
    SQLConvertedDate    datetime,
    ErrorMsg            varchar(255) )

INSERT INTO @TestTable (RFC822Date)
SELECT 'Wed, 02 Oct 2002 08:00:00 EST' UNION ALL
SELECT 'Thu, 19 Jul 2007 21:51:27 BST' UNION ALL
SELECT 'Wed, 16 Dec 2009 15:18:11 PDT' UNION ALL
SELECT 'Tue 9 Jun 2015 11:11:11 +0200' UNION ALL
SELECT 'Tue 9 Jun 2015 11:11:11 -0200' UNION ALL
SELECT 'Mon Jun 03 18:41:35' UNION ALL
SELECT 'Mon May 20 21:47:39' UNION ALL
SELECT 'Tue Jun 04 06:37:23' UNION ALL
SELECT 'Mon Jun 03 16:00:02' UNION ALL
SELECT 'Tue Jun 04 00:23:45' UNION ALL
SELECT 'Tue Jun 04 04:47:02' UNION ALL
SELECT 'May 31 2015  9:00AM' UNION ALL
SELECT 'May 31 2015  9:00AM EST'

/* -------------------------------------------------------------------- */
/*  Convert the dates above to datetime format using udf_ConvertTime.   */
/* -------------------------------------------------------------------- */

UPDATE  @TestTable
SET     MyConvertedDate = dbo.udf_ConvertTime(RFC822Date, 'LOCAL')

/* -------------------------------------------------------------------- */
/*  Try to convert these dates with the regular SQL CONVERT function.   */
/* -------------------------------------------------------------------- */

DECLARE @Seq smallint, @Max smallint
SELECT  @Seq = 0, @Max = MAX(Seq) FROM @TestTable
WHILE @Seq < @Max
BEGIN
    SET @Seq = @Seq + 1

    BEGIN TRY
        UPDATE @TestTable
        SET    SQLConvertedDate = CONVERT(datetime, RFC822Date)
        WHERE  Seq = @Seq
    END TRY
    BEGIN CATCH
        UPDATE @TestTable
        SET    ErrorMsg = ERROR_MESSAGE()
        WHERE  Seq = @Seq
    END CATCH
END

/* -------------------------------------------------------------------- */
/*  Show us the final results.                                          */
/* -------------------------------------------------------------------- */

SELECT * FROM @TestTable

Open in new window

The results should look like this:

Results.jpg
Hope you find this useful.

Footnotes:
1 Section 5 allowable date formats are:
 
date-time   =  [ day "," ] date time        ; dd mm yy
                                            ;  hh:mm:ss zzz

day         =  "Mon"  / "Tue" /  "Wed"  / "Thu"
            /  "Fri"  / "Sat" /  "Sun"

date        =  1*2DIGIT month 2DIGIT        ; day month year
                                            ;  e.g. 20 Jun 82

month       =  "Jan"  /  "Feb" /  "Mar"  /  "Apr"
            /  "May"  /  "Jun" /  "Jul"  /  "Aug"
            /  "Sep"  /  "Oct" /  "Nov"  /  "Dec"

time        =  hour zone                    ; ANSI and Military

hour        =  2DIGIT ":" 2DIGIT [":" 2DIGIT]
                                            ; 00:00:00 - 23:59:59

zone        =  "UT"  / "GMT"                ; Universal Time
                                            ; North American : UT
            /  "EST" / "EDT"                ;  Eastern:  - 5/ - 4
            /  "CST" / "CDT"                ;  Central:  - 6/ - 5
            /  "MST" / "MDT"                ;  Mountain: - 7/ - 6
            /  "PST" / "PDT"                ;  Pacific:  - 8/ - 7
            /  1ALPHA                       ; Military: Z = UT;
                                            ;  A:-1; (J not used)
                                            ;  M:-12; N:+1; Y:+12
            / ( ("+" / "-") 4DIGIT )        ; Local differential
                                            ;  hours+min. (HHMM)

Open in new window






Thank you for reading my article. Your feedback is welcome. If you liked this article or are interested in more like these, please click the Yes button for: Was this article helpful?

I look forward to hearing from you.
1
Comment
Author:dsacker
2 Comments
 
LVL 20

Author Comment

by:dsacker
Appreciate that. And thanks to Eric. He gave me some good pointers along the way.
0
 
LVL 66

Expert Comment

by:Jim Horn
I ran the code blocks and the result was excellent.  Nice job.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Join & Write a Comment

This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Other articles by this author
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month