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.


Sriram
Posted
Updated 27-Feb-14 3:08am
v2
Comments
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.
   
Comments
Maciej Los 27-Feb-14 5:14am
   
5!
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!
Hi,

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.

Example:
SQL
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).
   
Comments
Maciej Los 27-Feb-14 8:00am
   
Great answer! I forgot about that ;(
+5!
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
   
Thanks.
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')


or

SQL
SELECT CONVERT(datetime,
               SWITCHOFFSET(CONVERT(datetimeoffset,
                                    '2013-10-10T10:20:30+05:30'),
                            DATENAME(TzOffset, SYSDATETIMEOFFSET())))
       AS ColumnInLocalTime
   
v2
Comments
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