CREATE FUNCTION dbo.udf_ParseISO8601DateTime(@chvISO8601DateTime VARCHAR(33)) RETURNS DATETIME WITH RETURNS NULL ON NULL INPUT AS
/*^
* Function: udf_ParseISO8601DateTime
* Author: Rhys Jones, rhys@rmjcs.com
*
* Description: Parse an ISO8601 DateTime string and return a SQL DateTime.
* If a timezone is provided then the datetime is adjusted to return a UTC datetime.
*
* Parameters : @chvISO8601DateTime, i.e. 2006-09-07T00:00:00.0000000+00:00
* or 2006-09-07T00:00:00
*
* Notes : 1) Truncates the 4th to 7th digits of the milliseconds. NOT rounds.
*
* History:
* Date Who Ver Desc
* 04 Sep 2006 RMJ 0.1 Initial version.
*
^*/
BEGIN
DECLARE @dtResult AS DATETIME
DECLARE @intTimeZoneHours AS INT
DECLARE @intTimeZoneMinutes AS INT
DECLARE @intTimeZoneDirection AS INT
-- If input is Null then return null.
IF (@chvISO8601DateTime IS NULL)
BEGIN
SET @dtResult = NULL
END
-- Validate format of input string.
ELSE IF (@chvISO8601DateTime NOT LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9][0-9][+-][0-9][0-9]:[0-9][0-9]')
-- 2 0 0 7 - 0 9 - 0 2 T 0 0 : 0 0 : 0 0 . 0 0 0 0 0 0 0 + 0 0 : 0 0
AND (@chvISO8601DateTime NOT LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
BEGIN
-- If the input is not in the right format, raise an error.
SET @dtResult = CAST('error' AS DATETIME) -- Use this to get round the fact that you can't use raiserror
-- inside T/SQL functions. Causes error 241, "Conversion failed
-- when converting datetime from character string."
END
ELSE
BEGIN
-- Extract the main part of the datetime.
SET @dtResult = CONVERT(DATETIME, SUBSTRING(@chvISO8601DateTime, 1, 23), 126)
-- Extract the hours part of the timezone offset.
SET @intTimeZoneHours = CAST(SUBSTRING(@chvISO8601DateTime, 29, 2) AS INT)
-- Extract the minutes part of the timezone offset.
SET @intTimeZoneMinutes = CAST(SUBSTRING(@chvISO8601DateTime, 32, 2) AS INT)
-- Extract the direction of the timezone offset.
SET @intTimeZoneDirection = CAST(SUBSTRING(@chvISO8601DateTime, 28, 1) + '1' AS INT)
-- Add (or subtract) the hours part of the timezone offset.
SET @dtResult = DATEADD(hh, -@intTimeZoneDirection * @intTimeZoneHours, @dtResult)
-- Add (or subtract) the minutes part of the timezone offset.
SET @dtResult = DATEADD(mi, -@intTimeZoneDirection * @intTimeZoneMinutes, @dtResult)
END
RETURN @dtResult
END
go
-- Tests / Examples
SELECT
dbo.udf_ParseISO8601DateTime('1753-01-01T00:00:00.0000000+00:00') AS MinDate,
dbo.udf_ParseISO8601DateTime('9999-12-31T23:59:59.9979999+00:00') AS MaxDate,
dbo.udf_ParseISO8601DateTime('1900-01-01T01:02:03.0000000+01:00') AS ZeroDate
SELECT
dbo.udf_ParseISO8601DateTime('1753-01-01T00:00:00') AS MinDate,
dbo.udf_ParseISO8601DateTime('9999-12-31T23:59:59') AS MaxDate,
dbo.udf_ParseISO8601DateTime('1900-01-01T00:00:00') AS ZeroDate
SELECT
CONVERT(DATETIME,'1753-01-01T00:00:00',126) AS MinDate,
CONVERT(DATETIME,'9999-12-31T23:59:59',126) AS MaxDate,
CONVERT(DATETIME,'1900-01-01T00:00:00',126) AS ZeroDate
go