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

one way if you are using datetimes run this in a window

declare @date varchar(100)
select @date = '2009-06-26 14:30:00.000'

select dateadd(hh,datediff(hh,getdate(),getutcdate()),@date)

output 2009-06-26 18:30:00.000

better to just use getutcdate() all the time and store the users offset in his profile

SQL Server 2008 has new datetimeoffset data type which makes this much easier

now here is an answer that will work with the data you have (I added the 1/2 hour code also)

How the code works is explained here: Adding time offsets passed in to a datetime to generate localized datetime

    declare @date varchar(100),@multiplier int

select @date = '2009-06-26 14:30:00.000Z+4:30'
select @multiplier = case when @date like '%+%' then -1 else 1 end


select dateadd(mi, @multiplier *convert(int,right(@date,2)),dateadd(hh
    ,-1 * convert(int,replace(substring(@date,patindex('%z%',@date)+ 1,3),':',''))
    ,left(@date,23)))
go


--2009-06-26 10:00:00.000

declare @date varchar(100),@multiplier int

select @date = '2009-06-26 14:30:00.000Z-4:30'
select @multiplier = case when @date like '%+%' then -1 else 1 end

select dateadd(mi, @multiplier *convert(int,right(@date,2)),dateadd(hh
    ,-1 * convert(int,replace(substring(@date,patindex('%z%',@date)+ 1,3),':',''))
    ,left(@date,23)))
go

--2009-06-26 19:00:00.000

declare @date varchar(100),@multiplier int

select @date = '2009-06-26 14:30:00.000Z+14:30'
select @multiplier = case when @date like '%+%' then -1 else 1 end

select dateadd(mi, @multiplier *convert(int,right(@date,2)),dateadd(hh
    ,-1 * convert(int,replace(substring(@date,patindex('%z%',@date)+ 1,3),':',''))
    ,left(@date,23)))
go

--2009-06-26 01:00:00.000


declare @date varchar(100),@multiplier int
select @date = '2009-06-26 14:30:00.000Z-14:30'
select @multiplier = case when @date like '%+%' then -1 else 1 end

select dateadd(mi, @multiplier *convert(int,right(@date,2)),dateadd(hh
    ,-1 * convert(int,replace(substring(@date,patindex('%z%',@date)+ 1,3),':',''))
    ,left(@date,23)))
go

--2009-06-27 05:00:00.000

Answer 2

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