在SQL Server中将varchar转换为datetime

如何在SQL Server 2008中将 mmddyyyy 格式的字符串转换为 datetime

我的目标栏位于 DateTime

我已尝试使用 Convert 和大多数 Date 样式值但是我收到一条错误消息:

'将varchar数据类型转换为日期时间数据类型会导致超出范围的值 .

回答(13)

2 years ago

OP想要mmddyy,普通的转换器不适用于此:

select convert(datetime,'12312009')

Msg 242, Level 16, State 3, Line 1 
The conversion of a char data type to a datetime data type resulted in 
an out-of-range datetime value

试试这个:

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

OUTPUT:

-----------------------
2009-12-31 00:00:00.000

(1 row(s) affected)

2 years ago

SQL Server可以隐式地将“YYYYMMDD”形式的字符串转换为日期时间 - 必须显式转换所有其他字符串 . 这里有两个快速代码块,它们将从您正在讨论的表单进行转换:

版本1使用单位变量:

BEGIN 
DECLARE @input VARCHAR(8), @mon CHAR(2), 
@day char(2), @year char(4), @output DATETIME

SET @input = '10022009'   --today's date


SELECT @mon = LEFT(@input, 2), @day = SUBSTRING(@input, 3,2), @year = RIGHT(@input,4)

SELECT @output = @year+@mon+@day 
SELECT @output 
END

版本2不使用单位变量:

BEGIN 
DECLARE @input CHAR(8), @output DATETIME
SET @input = '10022009' --today's date 

SELECT @output = RIGHT(@input,4) + SUBSTRING(@input, 3,2) + LEFT(@input, 2)

SELECT @output
END

这两种情况都依赖于sql server执行隐式转换的能力 .

2 years ago

我发现这对我的转换很有帮助,没有字符串操作 . https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

CONVERT(VARCHAR(23), @lastUploadEndDate, 121)

yyyy-mm-dd hh:mi:ss.mmm(24h)是我需要的格式 .

2 years ago

转换将是正常答案,但格式不是转换器的可识别格式,mm / dd / yyyy可以使用convert(datetime,yourdatestring,101)进行转换,但是您没有这种格式,因此它失败了 .

问题是格式是非标准的,您必须将其操作为转换器可以从可用的标准中理解的标准 .

如果你能保证这种格式,就一起黑了

declare @date char(8)
set @date = '12312009'
select convert(datetime, substring(@date,5,4) + substring(@date,1,2) + substring(@date,3,2),112)

2 years ago

可能你有不能转换的坏数据 . 日期永远不应存储在varchar中,因为它将允许日期,如ASAP或02/30/2009 . 使用数据上的isdate()函数查找无法转换的记录 .

好吧,我测试了已知良好的数据,仍然得到了消息 . 你需要转换为不同的格式,因为它不知道12302009是mmddyyyy还是ddmmyyyy . yyyymmdd的格式不明确,SQL Server将正确转换它

我得到了这个工作:

cast( right(@date,4) + left(@date,4) as datetime)

如果您有任何非标准格式(如“112009”)或某些文本值或真实的超出范围日期,您仍会收到错误消息 .

2 years ago

看看BOL中的 CAST / CONVERT 应该是一个开始 .

如果您的目标列是 datetime ,您不需要转换它,SQL将为您执行此操作 .

除此以外

CONVERT(datetime, '20090101')

应该这样做 .

这是一个应该有帮助的link

2 years ago

我有类似的运气:

Convert(DATETIME, CONVERT(VARCHAR(2), @Month) + '/' + CONVERT(VARCHAR(2), @Day)
+ '/' + CONVERT(VARCHAR(4), @Year))

2 years ago

我使用 STUFF 插入分隔字符然后使用 CONVERT 与适当的样式 . 像这样的东西:

DECLARE @dt VARCHAR(100)='111290';
SELECT CONVERT(DATETIME,STUFF(STUFF(@dt,3,0,'/'),6,0,'/'),3)

首先你使用两次 STUFF 来获得11/12/90而不是111290,而不是使用 3 将其转换为 datetime (或任何其他拟合格式:使用 . 代表德语, - 代表英国......)More details on CAST and CONVERT

最好的是,正确存储日期和时间值 .

  • 这应该是"universal unseparated format" yyyyMMdd

  • 或(特别是在XML中)它应该是ISO8601: yyyy-MM-ddyyyy-MM-ddThh:mm:ss More details on ISO8601

任何文化特定的格式迟早都会引发麻烦......

2 years ago

use Try_Convert:如果转换成功,则返回强制转换为指定数据类型的值;否则,返回null .

DECLARE @DateString VARCHAR(10) ='20160805'
SELECT TRY_CONVERT(DATETIME,@DateString)

SET @DateString ='Invalid Date'
SELECT TRY_CONVERT(DATETIME,@DateString)

链接:MSDN TRY_CONVERT (Transact-SQL)

2 years ago

我认为CONVERT是最好的选择,因为你可以包含一个样式(日期格式),所以美国的默认值是110,这是mm-dd-yyyy .

2 years ago

这个问题的根本原因可能是区域设置 - 数据库等待YYYY-MM-DD,而应用程序就像在我的情况下那样查看DD-MM-YYYY(俄语语言环境格式) . 我所做的一切 - 将语言环境格式从俄语改为英语(美国)和voilà .

2 years ago

这似乎是最简单的方法..

SELECT REPLACE(CONVERT(CHAR(10), GETDATE(), 110),'-','')

2 years ago

插入或更新时的SQL标准日期必须在1/1/1753 12:00:00 AM和12/31/9999 11:59:59 PM之间 .

因此,如果您在17/1/1753以下插入/更新,您将收到此错误 .