Click here to Skip to main content
15,867,568 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 2: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.
 
Share this answer
 
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).
 
Share this answer
 
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.
 
Share this answer
 
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
 
Share this answer
 
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