RMJCS Logo
 

dbo.udf_ParseISO8601DateTime

 
 
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
 
     
 

Parse ISO 8601 DateTime

 
 

If you're processing XML produced by a .Net serialiser using T/SQL this function will reliably convert an ISO 8601 DateTime to a SQL DateTime.

This function is suitable for SQL Server 2005 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option). The function works in SQL 2008 although the full ISO 8601 datetime strings are now supported directly via the new DATETIME2 and DATETIMEOFFSET datatypes.

 
     
Copyright 2007-2015 RMJCS Ltd