Converting Character to DATETIME

Question

Converting Character to DATETIME

anithagovindarajalu

SSC Eights!

Points: 981
More actions
May 29, 2009 at 5:45 am

#210064

Hi,
Can anyone help me to convert the character to DATETIME
SELECT Convert(datetime,'2009-02-26T00:00:00.0000000-08:00',126)
gives Error as "Conversion failed when converting datetime from character string".
Thanks,
Anitha

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

It depends whether or not you want to convert all the times so that they are relative to the same timezone and are therefore more easily comparable. If so, and the datetime strings are expected to be from different timezones, you need to decide on a reference timezone when converting to a datetime value in SQL Server.

In the example below, all the time strings are converted to datetime values for the Pacific time zone (US & Canada).

/* Define reference time zone */

DECLARE @ReferenceTimeZoneOffset int /* minutes */

SELECT @ReferenceTimeZoneOffset = -8 * 60 /* Pacific time (US & Canada) */

SELECT

TimeZone,

TimeString,

DATEADD(minute, (

CASE WHEN (RIGHT(TimeString, 6) LIKE '+[0-2][0-9]:[0-5][0-9]') THEN

@ReferenceTimeZoneOffset - DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(TimeString, 5)))

WHEN (RIGHT(TimeString, 6) LIKE '-[0-2][0-9]:[0-5][0-9]') THEN

@ReferenceTimeZoneOffset + DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(TimeString, 5)))

ELSE 0 END),

CONVERT(datetime, SUBSTRING(TimeString, 1, 23))) AS ConvertedTime

FROM (

SELECT '2009-02-26T00:00:00.0000000-08:00', 'Pacific time (US)'

UNION ALL

SELECT '2009-02-26T18:30:00.0000000-05:00', 'Eastern time (US)'

UNION ALL

SELECT '2009-02-26T10:15:00.0000000-00:00', 'Greenwich Mean Time'

) TestData(TimeString, TimeZone)

If all the stored date/time strings are from the same time zone or you only require the local time (relative to wherever the date time value was recorded), then you can just ignore the timezone offset when converting the string to a datetime, as follows:

SELECT

TimeZone,

TimeString,

CONVERT(datetime, SUBSTRING(TimeString, 1, 23)) AS ConvertedTime

FROM (

SELECT '2009-02-26T00:00:00.0000000-08:00', 'Pacific time (US)'

UNION ALL

SELECT '2009-02-26T18:30:00.0000000-05:00', 'Eastern time (US)'

UNION ALL

SELECT '2009-02-26T10:15:00.0000000-00:00', 'Greenwich Mean Time'

) TestData(TimeString, TimeZone)

Note that The above datetime conversions do not take account of possible daylight savings time offsets.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Different take on the same thing...

DECLARE @DateString VARCHAR(50)

SET @DateString = '2009-02-26T00:00:00.0000000-08:00'

SELECT CONVERT(DATETIME,LEFT(@DateString,23),126)

+ CAST(RIGHT(@DateString,5) AS DATETIME)

I couldn't remember if you add or subtract the TZ offset. In this case, adding a minus 8 hours is the same as subtracting 8 hours.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions