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
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.
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
The results should look like this:
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)
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.
Comments (2)
Author
Commented:Commented: