none
Convert To Date

    Question

  • How does one convert to a Date from this string that represents a Date?

     

    '2007-02-28T01:11:12+10:00'

     

    In fact what is the time stating? is it 11:12 or 10:00? But more importantly, how do you convert it to Datetime Datatype to Insert into SQL Server 2005?

    Thursday, March 13, 2008 2:27 AM

Answers

  • 01:11:12 is your UTC time & +10:00 is the region offset. So, you have to add the 10 hours on the 01:11:12 so you will  get 11:11:12 is the final time. But, as of now SQL Server 2005 won’t support DATETIMEOFFSET but in SQL Server 2008 we have this new datatype where you can enter your string value directly into the datetime column. So, you have to parse the string to get the proper date.

    Code Snippet

    Create Function GetDateTimeOffsetValue(@DateString as varchar(100))

    returns datetime

    as

    Begin

     

      Declare @TimeOffset as varchar(100)

      Declare @OffsetCharIndex as int

      Declare @OffsetChar as char

      Declare @OffsetInMin as Int

     

     

     

      Set @OffsetCharIndex = 20

      Set @OffsetChar = Substring(@DateString, @OffsetCharIndex, 1)

       

     

      Set @TimeOffset  = case when @OffsetChar IS NOT NULL

                then Substring(@DateString,@OffsetCharIndex+1,len(@DateString)-@OffsetCharIndex+1) end

     

      Set @DateString = case when @OffsetChar IS NOT NULL THEN Substring(@DateString,1,@OffsetCharIndex-1) end

     

     

      select @OffsetInMin = cast(@OffsetChar + cast(datediff(Mi,'00:00:00.000', cast(@TimeOffset as datetime)) as varchar) as int)

     

      Return DateAdd(Mi, @OffsetInMin, Convert(datetime,@DateString,126))

    End

     

    go

     

    select dbo.GetDateTimeOffsetValue('2007-02-28T01:11:12+05:30')

     

     

     

    Thursday, March 13, 2008 2:56 AM

All replies

  • BOL covers converting datetime to CHAR or VARCHAR.  Use index and look at CONVERT or CAST.  Hope this helps. 

    Best of Luck!!

     

    Code Snippet

    SELECT CONVERT(VARCHAR, GETDATE(), 0)

    SELECT CONVERT(VARCHAR, GETDATE(), 101)

    SELECT CONVERT(VARCHAR, GETDATE(), 102)

    SELECT CONVERT(VARCHAR, GETDATE(), 103)

    SELECT CONVERT(VARCHAR, GETDATE(), 104)

    SELECT CONVERT(VARCHAR, GETDATE(), 105)

    SELECT CONVERT(VARCHAR, GETDATE(), 106)

    SELECT CONVERT(VARCHAR, GETDATE(), 107)

    SELECT CONVERT(VARCHAR, GETDATE(), 108)

    SELECT CONVERT(VARCHAR, GETDATE(), 109)

    SELECT CONVERT(VARCHAR, GETDATE(), 110)

    SELECT CONVERT(VARCHAR, GETDATE(), 111)

    SELECT CONVERT(VARCHAR, GETDATE(), 112)

    SELECT CONVERT(VARCHAR, GETDATE(), 113)

    SELECT CONVERT(VARCHAR, GETDATE(), 114)

    SELECT CONVERT(VARCHAR, GETDATE(), 120)

    SELECT CONVERT(VARCHAR, GETDATE(), 121)

    SELECT CONVERT(VARCHAR, GETDATE(), 126)

    SELECT CONVERT(VARCHAR, GETDATE(), 130)

    SELECT CONVERT(VARCHAR, GETDATE(), 131)

     

     

    Thursday, March 13, 2008 2:32 AM
  • It's technically a UTC datetime.... 1:11 and 12 seconds, UTC + 10:00, if I'm not mistaken.

     

    Try this:

    Code Snippet

     

    SELECT CAST(Left('2007-02-28T01:11:12+10:00', CHARINDEX('T', '2007-02-28T01:11:12+10:00') - 1) + ' ' + SUBSTRING('2007-02-28T01:11:12+10:00', CHARINDEX('T','2007-02-28T01:11:12+10:00'

    ) + 1, 8) AS DATETIME)

     

     

     

    I used CHARINDEX instead of hardcoding in the lengths and substrings, in case the length of the date portion varied (ex. for single-digit days).

    Thursday, March 13, 2008 2:33 AM
  • Thanks.

     

    I see in your example that the 10:00 has no significance, what does the 10:00 mean?

     

    Thursday, March 13, 2008 2:39 AM
  • 01:11:12 is your UTC time & +10:00 is the region offset. So, you have to add the 10 hours on the 01:11:12 so you will  get 11:11:12 is the final time. But, as of now SQL Server 2005 won’t support DATETIMEOFFSET but in SQL Server 2008 we have this new datatype where you can enter your string value directly into the datetime column. So, you have to parse the string to get the proper date.

    Code Snippet

    Create Function GetDateTimeOffsetValue(@DateString as varchar(100))

    returns datetime

    as

    Begin

     

      Declare @TimeOffset as varchar(100)

      Declare @OffsetCharIndex as int

      Declare @OffsetChar as char

      Declare @OffsetInMin as Int

     

     

     

      Set @OffsetCharIndex = 20

      Set @OffsetChar = Substring(@DateString, @OffsetCharIndex, 1)

       

     

      Set @TimeOffset  = case when @OffsetChar IS NOT NULL

                then Substring(@DateString,@OffsetCharIndex+1,len(@DateString)-@OffsetCharIndex+1) end

     

      Set @DateString = case when @OffsetChar IS NOT NULL THEN Substring(@DateString,1,@OffsetCharIndex-1) end

     

     

      select @OffsetInMin = cast(@OffsetChar + cast(datediff(Mi,'00:00:00.000', cast(@TimeOffset as datetime)) as varchar) as int)

     

      Return DateAdd(Mi, @OffsetInMin, Convert(datetime,@DateString,126))

    End

     

    go

     

    select dbo.GetDateTimeOffsetValue('2007-02-28T01:11:12+05:30')

     

     

     

    Thursday, March 13, 2008 2:56 AM
  • 01:11:12 is your UTC time & +10:00 is the region offset. So, you have to add the 10 hours on the 01:11:12 so you will  get 11:11:12 is the final time.  

    Um that doesn't sound right. How does the T imply it is UTC time? "2007-02-28 01:11:12+10:00" means 1am IN THE TIMEZONE +10. So that would be equal to "2007-02-27 15:11:12+00:00" (i.e. 3pm previous day UTC time).

    Proof:
    Run this:
    SELECT SYSUTCDATETIME(), SYSDATETIMEOFFSET()
    It ADDS the timezone to the UTC time to get the local time. Not the other way around.
    Thursday, June 04, 2009 6:21 AM