Click here to Skip to main content
15,121,963 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to convert exactly this '2013-10-10T00:00:00+05:30' string to datetime in sql server 2005 with out using substring function,
but i got an error 'Conversion failed when converting datetime from character string'.plzz sugest me.

Thanks in advance.

Updated 27-Feb-14 3:08am
Richard MacCutchan 27-Feb-14 4:20am
Where are you trying the conversion, and what is the code you are using?

You can convert it to a DATETIMEOFFSET - a badly-named type that is really date+time+timezone:

SELECT CAST('2013-10-10T00:00:00+05:00' AS DATETIMEOFFSET)

Unfortunately, if you cast that to a datetime, it just truncates the offset rather than incorporate it into the datetime.

You can, however, use DATEPART to take the various components out and reassemble them into a DATETIME. I'll leave that as an exercise for the reader.
Maciej Los 27-Feb-14 5:14am
SriramNidamanuri 27-Feb-14 5:18am
Its Not Working....Error Message is 'Type DATETIMEOFFSET is not a defined system type' .
Rob Grainger 28-Feb-14 5:00am
I maintain that this was a solution to the original question - I even checked for support on SQL Server 2008 before submitting!

In your last comment you said that you're using SQL Server 2005, not 2008. In SQL Server 2005 you could use built-in XQuery functions to do such conversion.

SELECT CAST('' AS XML).value('xs:dateTime("2013-10-10T00:00:00+05:30")', 'DATETIME');

xs:dateTime function converts time to local time (based on the time zone).
Maciej Los 27-Feb-14 8:00am
Great answer! I forgot about that ;(
Andrius Leonavicius 27-Feb-14 8:03am
Thank you, Maciej.
Tom Marvolo Riddle 27-Feb-14 8:17am
Good one 5!
Andrius Leonavicius 27-Feb-14 8:31am
Please, have a look at solution 1 by Rob Grainger. In addition of above information, i would suggest to read this article: CAST and CONVERT (T-SQL)[^]. There you'll find many usefull information about converting one data type into another. Some explicit conversions are not supported.
try the below ..

SELECT CONVERT(datetimeoffset,'2013-10-10T00:00:00+05:30')


                            DATENAME(TzOffset, SYSDATETIMEOFFSET())))
       AS ColumnInLocalTime
SriramNidamanuri 27-Feb-14 5:46am
My Version is sql server 2005 ,Thats why ist not woking

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900