首页 文章

在SQL Server中将varchar转换为datetime

提问于
浏览 328
59

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

我的目标栏位于 DateTime

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

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

13 回答

  • 70

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

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

  • 7

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

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

    我得到了这个工作:

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

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

  • 5

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

  • 4

    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执行隐式转换的能力 .

  • 4

    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)

  • 3

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

  • 1

    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)
    
  • 1

    我使用 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

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

  • 1

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

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

    我发现这对我的转换很有帮助,没有字符串操作 . 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)是我需要的格式 .

  • 0

    转换将是正常答案,但格式不是转换器的可识别格式,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)
    
  • 0

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

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

    除此以外

    CONVERT(datetime, '20090101')
    

    应该这样做 .

    这是一个应该有帮助的link

  • 0

    我有类似的运气:

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

相关问题