SQL Server - Converting RFC822 Dates Into Any Timezone

dsackerContract ERP Admin/Consultant
CERTIFIED EXPERT
Published:
Updated:
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
4,090 Views
dsackerContract ERP Admin/Consultant
CERTIFIED EXPERT

Comments (2)

dsackerContract ERP Admin/Consultant
CERTIFIED EXPERT

Author

Commented:
Appreciate that. And thanks to Eric. He gave me some good pointers along the way.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
I ran the code blocks and the result was excellent.  Nice job.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.