SQL - 将varchar数据类型转换为datetime数据类型时导致值超出范围

Question

当我运行一个SQL将我的数据类型值从varchar转换为datetime时，我一直遇到以下错误:

Msg 242，级别16，状态3，第1行 将varchar数据类型转换为datetime数据类型导致超出范围的值。

我已经检查了数据，但没有发现任何问题：运行以下检查并全部未返回结果

SELECT [Date] from table where [DATe] is null
SELECT [Date] from table where [DATe] = ''
SELECT [Date] from table where LEN([date])> 10
SELECT [Date] from table where LEN([date])< 10
SELECT top 100 [Date] , SUBSTRING([date],4,2) from [table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 12
SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 31

还有什么其他值得关注的地方，也许需要一些建议或帮助解决这个问题？似乎无法找到问题的根源。

- user23495

---

日期列的数据类型是什么？您可以向我展示表格结构以及出现错误的语句吗？ - Spikeh

你提供的六个SQL语句中，哪一个失败了？ - Mureinik

这六个语句都能正常工作，并验证了数据没有问题。 - user23495

您还没有检查不合法的日期，例如2013-10-31或2013-02-30。可能，您遇到的错误与这种有问题的日期有关。 - Dalen

嗨，Dalen，我已经完成了这个检查。数据设置的方式是31/10/2013、30/10/2013。它是以英国格式设置的。当尝试更改列类型时，这会产生任何影响吗？我认为不会。 - user23495

Answer 1

一周前，我遇到了同样的问题，问题出在时区设置上。请使用其他格式，例如mm/dd/yyyy（通常有效）。

对我而言，将日期指定为30/12/2013会导致错误。不过，指定为mm/dd/yyyy格式就可以正常工作。

如果需要转换输入，则可以尝试使用CONVERT方法。语法如下：

CONVERT(VARCHAR,@your_date_Value,103)

CONVERT(VARCHAR, '12/30/2013', 103)

最后的103是日期时间格式。

有关转换格式和更多信息，请参考此链接：https://www.w3schools.com/sql/func_sqlserver_convert.asp

- Mahe

---

谢谢你的帮助，伙计。我尝试转换它，但仍然没有成功。这可能是因为表仍然是一个varchar类型，或者还有其他原因导致失败吗？ - user23495

如果您发布样本数据（在表格中），将会非常有帮助。在评论中，您表示希望以“年-月-日”的格式呈现。因此，请尝试这个命令：SELECT CONVERT(char(10), GetDate(),126)，只需用必要的值替换 GETDATE() 即可。 - Mahe

CONVERT(DATETIME,'2022-05-06',120) 对我有效。 - Michael Freidgeim

Answer 2

我因为一个愚蠢的错误遇到了这个问题。确保日期实际存在！

例如：

2015年9月31日是不存在的。

EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150931'

所以它失败并显示以下消息：

Error converting data type varchar to datetime.

要修复它，请输入一个有效的日期：

EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150930'

它可以正常执行。

- Mr. C

---

是的，我刚在我要处理的数据库中发现了一个截止日期为2015年2月29日。我想知道它是怎么进去的。我不知道还有多少类似的数据... - Resource

刚才使用了cast(SUBSTRING([MyDateField],1,2) as integer) > 31，并发现12月60日的记录。是谁输入这种东西，像苏斯博士一样？ - SteveCav

谢谢！这就是我的问题所在。我的数据集中有些是错误的数据 - 01/01/1113，哈哈。 - Sev09

对我来说，在构建SQL语句时，我在格式化日期时放错了格式字符串。我使用了Format(DateTime.Now, "yyyymmdd")，但正确的应该是Format(DateTime.Now, "yyyyMMdd")。 - Jay Imerman

啊，美国的日期约定！让我困惑了一段时间，因为“2017年10月26日”，即“26-10-2017”是一个完全有效的日期 :) - Antimony

Answer 3

最近我遇到了类似的问题。应用程序和数据库服务器中的区域设置都正确设置。然而，执行 SQL 后出现了以下错误：

"将 varchar 数据类型转换为 datetime 数据类型时，结果值超出范围"。

问题出在数据库用户的默认语言上。

要在 SSMS 中检查或更改它，请前往“安全性”->“登录名”，右键单击运行查询的用户的用户名。选择属性->常规，并确保对话框底部的默认语言是您期望的。

对于所有运行查询的用户都要重复此操作。

- ali

---

这对我很有帮助。只是一个小修正：server login 的默认语言而不是 db user。https://www.top-password.com/blog/difference-between-sql-server-logins-and-database-users/ - Baz Guvenkaya

由于它设置在程序代码中，而我没有访问代码的权限，所以我将其从“英语”更改为“英式英语”，然后它就起作用了！ - vaheeds

对我来说也一样，我不得不将英语改为英式英语 - 阿里，你真是救星！ - david-giorgi

我确实这样做了，我将英语改为英式英语，然后它就起作用了。 - Zubeid Hendricks

Answer 4

你可以利用。

Set dateformat <date-format> ;

在你的存储过程或存储函数中完成所需操作。

- Sachin Mishra

---

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-2017 - arlak

这解决了我的问题。我不明白的是为什么突然开始发生这种情况 :( - VictorEspina

Answer 5

Create procedure [dbo].[a]

@examdate varchar(10) ,
@examdate1 varchar(10)
AS
Select tbl.sno,mark,subject1,
Convert(varchar(10),examdate,103) from tbl
where 
(Convert(datetime,examdate,103)  >= Convert(datetime,@examdate,103) 
and (Convert(datetime,examdate,103) <=  Convert(datetime,@examdate1,103)))

- sonu yadav

---

请为你的回答添加更多描述。这将有助于提问者更好地理解你的回答。 - Pramod S. Nikam

Answer 6

这是因为SQL有时无法识别dd/mm/yyyy格式。因此，我们应始终检查输入字符串是否为有效日期，然后相应地将其转换为mm/dd/yyyy。下面我展示了如何完成这项工作，我创建了一个函数来重新排列dd/mm/yyyy为mm/dd/yyyy

select case when isdate('yourdate')=1 then CAST('yourdate' AS datetime) 
  else (select * from dbo.fn_convertdate(yourdate))

Create function dbo.fn_convertdate( @Stringdate nvarchar(29))
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
)
Begin
Declare @table table(id int identity(1,1), data varchar(255))
Declare @firstpart nvarchar(255)
Declare @tableout table(id int identity(1,1), data varchar(255))

Declare @Secondpart nvarchar(255)
Declare @Thirdpart nvarchar(255)

declare @date datetime

insert into @table
select * from dbo.fnSplitString(@Stringdate,'/')
select @firstpart=data from @table where id=2
select @Secondpart=data from @table where id=1
select @Thirdpart=data from @table where id=3
set @date=@firstpart+'/'+@Secondpart+'/'+@Thirdpart
insert into @output(splitdata) values(
@date)


return
End

- shashank m

---

遇到了一个问题，日期字符串为'19610010'（格式：YYYYMMDD），导致出现“将nvarchar数据类型转换为datetime数据类型时导致超出范围的值”错误。 SELECT CONVERT(datetime, 'yourdate') FROM [yourtable] WHERE ISDATE('yourdate')=1 挽救了局面 :) - J Pollack

Answer 7

正如你所知道的，这是英国日期格式的问题。您可以使用函数间接地进行日期转换。

CREATE FUNCTION  ChangeDateFormatFromUK
( 
   @DateColumn varchar(10)
)

RETURNS VARCHAR(10)
AS 
 BEGIN
    DECLARE @Year varchar(4), @Month varchar(2), @Day varchar(2), @Result varchar(10)
    SET @Year = (SELECT substring(@DateColumn,7,10))
    SET @Month = (SELECT substring(@DateColumn,4,5)) 
    SET @Day = (SELECT substring(@DateColumn,1,2))
    SET @Result  = @Year  + '/' + @Month + '/' +  @Day

 RETURN @Result
END

调用该函数。

SELECT dbo.ChangeDateFormatFromUK([dates]) from table

将其正常转换为日期时间

SELECT CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) FROM TABLE

在您的情况下，您可以：

SELECT [dates] from table where CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) > GetDate()   -- or any date

- Sumit

Answer 8

在顶部添加：

SET DATEFORMAT ymd; 

或者你在查询中使用的任何格式

- stephen kilonzo

---

这解决了我的问题。不知何故，我的SQL Server无法从字符串格式（dd/MM/yyyy）中正确解析格式化良好的欧洲日期。感谢Stephen。点赞 :-) - Mariusz

在我的情况下，我使用了SET DATEFORMAT dmy;问题得到了解决。 - edsonlp1

Answer 9

我曾经遇到过同样的问题，并发现这个问题是由于 SQL Server 在将字符转换为整数进行比较时并不以相同方式处理。在我的测试中，我发现一些转换后字符（例如惊叹号）的比较会返回类型转换错误，而其他转换后字符（例如空格）的比较将被确定为超出范围。

以下示例代码测试了不同的情况，并使用嵌套 REPLACE 语句提供了解决方案。REPLACE 确定字符串中是否存在任何非数字或斜杠的字符，如果存在，则字符串的长度将大于零，从而表明存在“错误”字符，日期无效。

DECLARE @str varchar(10)
SET @str = '12/10/2012'
IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
    PRINT @str+': Passed Test'
    ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/10/2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string
PRINT ''
GO

DECLARE @str varchar(10)
SET @str = '12/!0/2012'
    IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
        PRINT @str+': Passed Test'
        ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/!0/2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string
PRINT ''
GO

DECLARE @str varchar(10)
SET @str = '12/  /2012'
IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
    PRINT @str+': Passed Test'
    ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/  /2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string

输出：

--Output
--12/10/2012: Passed Test
--Number of characters in 12/10/2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 0

--Msg 245, Level 16, State 1, Line 4
--Conversion failed when converting the varchar value '!0' to data type int.
--Number of characters in 12/!0/2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 1

--12/  /2012: Failed Test
--Number of characters in 12/  /2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 2

- RSax

Answer 10

我曾遇到类似问题，经过调查发现数据库中存在非常旧的日期。

因此，我发现将日期转换为日期时间仅适用于1753-01-01（含）之后的日期。

select CONVERT(Datetime, '1753-01-01', 103) as RESULT
--1753-01-01 00:00:00.000

select CONVERT(Datetime, '1752-12-31', 103) as RESULT
--The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

- CoPLaS