Converting from globalized date/time format (maybe ISO8601) to SQL Server datetime type

Question

Is there a string format to represent a datetime that SQL will be able to parse and convert into another offset (EST -> UTC for example).

I have a string from the user such as:

declare @p1 varchar(50);
declare @utcDateTime datetime;

set @p1 = "2009-06-26 14:30:00.000Z-4:00";   -- could be ISO8601

-- what do I do here to convert @p1?

set @utcDateTime = -- should be "2009-06-26 18:30:00.000"

I want to be able to convert the string to its UTC equivalent and store it in a datetime field. Such that:

select @ utcDateTime 

should yield this:

"2009-06-26 18:30:00.000"

In other words, I want to store a datetime that has the value of '2009-06-26 18:30', given the first string.

Also, we must assume the server is not in the same timezone as the user (so we can't just detect the offset datediff(gettime(), getutctime()).

I have tried using convert(...) and cast(... as datetime) but with no luck.

Is there a way to do this in SQL Server 2005?

Answer 1

OK here's my try at it - this was fun :-)

 DECLARE  @datestr varchar(100)

SET @datestr = '2009-06-26 14:30:00.000Z+4:00'

SELECT @datestr, DATEADD(mi, -1 * CAST(SUBSTRING(@datestr,25,1)+'1' AS int) *  
    DATEDIFF(mi,'1900-01-01', CAST(SUBSTRING(@datestr,26,5) as datetime)),   
    CAST(LEFT(@datestr,23) as datetime) )

SET @datestr = '2009-06-26 14:30:00.000Z-4:00'

SELECT @datestr, DATEADD(mi, -1 * CAST(SUBSTRING(@datestr,25,1)+'1' AS int) *  
    DATEDIFF(mi,'1900-01-01', CAST(SUBSTRING(@datestr,26,5) as datetime)),   
    CAST(LEFT(@datestr,23) as datetime) )

SET @datestr = '2009-06-26 14:30:00.000Z+14:00'

SELECT @datestr, DATEADD(mi, -1 * CAST(SUBSTRING(@datestr,25,1)+'1' AS int) *  
    DATEDIFF(mi,'1900-01-01', CAST(SUBSTRING(@datestr,26,5) as datetime)),   
    CAST(LEFT(@datestr,23) as datetime) )

SET @datestr = '2009-06-26 14:30:00.000Z+4:30'

SELECT @datestr, DATEADD(mi, -1 * CAST(SUBSTRING(@datestr,25,1)+'1' AS int) *  
    DATEDIFF(mi,'1900-01-01', CAST(SUBSTRING(@datestr,26,5) as datetime)),   
    CAST(LEFT(@datestr,23) as datetime) )

SET @datestr = '2009-06-26 14:30:00.000Z-4:30'

SELECT @datestr, DATEADD(mi, -1 * CAST(SUBSTRING(@datestr,25,1)+'1' AS int) *  
    DATEDIFF(mi,'1900-01-01', CAST(SUBSTRING(@datestr,26,5) as datetime)),   
    CAST(LEFT(@datestr,23) as datetime) )

SET @datestr = '2009-06-26 14:30:00.000Z+14:30'

SELECT @datestr, DATEADD(mi, -1 * CAST(SUBSTRING(@datestr,25,1)+'1' AS int) *  
    DATEDIFF(mi,'1900-01-01', CAST(SUBSTRING(@datestr,26,5) as datetime)),   
    CAST(LEFT(@datestr,23) as datetime) )

Returns:

2009-06-26 14:30:00.000Z+4:00  2009-06-26 10:30:00.000

2009-06-26 14:30:00.000Z-4:00  2009-06-26 18:30:00.000

2009-06-26 14:30:00.000Z+14:00 2009-06-26 00:30:00.000

2009-06-26 14:30:00.000Z+4:30  2009-06-26 10:00:00.000

2009-06-26 14:30:00.000Z-4:30  2009-06-26 19:00:00.000

2009-06-26 14:30:00.000Z+14:30 2009-06-26 00:00:00.000