在sql server中从String转换为Datetime [英] Convert from String to Datetime in sql server

问题描述

我想将这个'2013-10-10T00：00：00 + 05:30'字符串转换为sql server 2005中的日期时间而不使用子字符串函数，

但是我收到了一个错误'转换日期时间从字符串转换失败'.plzz sugest me。



提前致谢。





Sriram

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

推荐答案

你可以把它转换为DATETIMEOFFSET - 一个名字很糟糕的类型，实际上是日期+时间+时区：< br $>


SELECT CAST（'2013-10-10T00：00：00 + 05：00'AS DATETIMEOFFSET）



不幸的是，如果你将它转换为日期时间，它只是截断偏移量而不是将其合并到日期时间。



但是，您可以使用DATEPART将各种组件取出并重新组合成DATETIME。我将把它作为读者的练习。

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.

你在上次评论中说你'重新使用 SQL Server 2005 ，而不是2008.在SQL Server 2005中，您可以使用内置的XQuery函数进行此类转换。



示例：

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:

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

xs：dateTime function将时间转换为当地时间（基于时区）。

xs:dateTime function converts time to local time (based on the time zone).

请看看Rob Grainger的解决方案1。除了以上信息，我建议阅读这篇文章： CAST和CONVERT（T-SQL） [ ^ ]。在那里，您将找到许多有关将一种数据类型转换为另一种数据的有用信息。不支持某些显式转换。

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.

这篇关于在sql server中从String转换为Datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！