I recently needed to implement support within a SQL 2005 database for the RFC 822 DateTime format. A quick search of the Internet yielded several examples of how to convert a SQL DateTime value to its equivalent RFC 822 representation. I did not find any postings on how to convert an RFC 822 DateTime string to its equivalent SQL DateTime representation, so I ended up writing my own.

Converting from a SQL DateTime to the RFC 822 equivalent is basically just a string manipulation problem, which is easily solved using SQL's built-in string functions. Converting from an RFC 822 DateTime to the equivalent SQL DateTime involves using SQL string functions to build a Europe default + milliseconds (style 113) date-time string and then using the CONVERT function in SQL to get the SQL DateTime.

Provided below are the scalar SQL user-defined functions you can utilize for handling the RFC 822 DateTime format within your database statements or stored procedures.

dbo.ToRfc822DateTime Function:

CREATE FUNCTION [dbo].[ToRfc822DateTime]
(
@Date    DATETIME
)
RETURNS NVARCHAR(70)
AS
BEGIN
RETURN (LEFT(DATENAME(dw, @Date),3) + ', ' + STUFF(CONVERT(nvarchar,@Date,113),21,4,' GMT'))
END

dbo.FromRfc822DateTime Function:

CREATE FUNCTION [dbo].[FromRfc822DateTime]
(
@RfcDate    NVARCHAR(70)
)
RETURNS DATETIME
AS
BEGIN
----------------------------------------------------------
--    Remove GMT designator
----------------------------------------------------------
SELECT    @RfcDate    = REPLACE(@RfcDate, ' GMT', '')
----------------------------------------------------------
--    Return RFC 822 format as SQL DateTime
----------------------------------------------------------
RETURN CONVERT(
DATETIME
,SUBSTRING(@RfcDate, 6, 2)    + ' ' + 
SUBSTRING(@RfcDate, 9, 3)    + ' ' + 
SUBSTRING(@RfcDate, 13, 4)    + ' ' + 
SUBSTRING(@RfcDate, 18, LEN(@RfcDate) - 17)
,113
)
END