SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

小开

I have faced the same problem a week ago. 问题在于时区设置。请用其他格式指定，如 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

小开

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

小开

我遇到过同样的问题，并且确定出现这个问题是因为 SQLServer 不以相同的方式对转换为整数的字符执行比较。在我的测试中，我发现一些转换后字符的比较，比如叹号，会返回类型转换错误，而其他转换后字符的比较，比如空格，则会被判定为超出范围。

此示例代码测试不同的可能场景，并提供使用嵌套 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

小开

由于一个愚蠢的错误，我遇到了这个问题

例如:

2015年9月31日并不存在。

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

因此，这种做法无法传递出这样的信息:

Error converting data type varchar to datetime.

要修复它，输入一个有效日期:

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

而且效果很好。

小开

这是因为 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

小开

我在自动向列插入 sysdate 时也遇到了这个问题。

What I did is I changed my system date format to match with SQL server's date format. 例如:。 my SQL format was mm/dd/yyyy and my system format was set to dd/mm/yyyy. 我将系统格式改为 mm/dd/yyyy，错误消失了

KB

小开

我只是将我想要转换成一个新表(带有 DateTime 文件)的 varchar 字段转换成一个兼容 DateTime 的布局，然后 SQL 将毫无问题地完成从 varchar 到 DateTime 的转换。

在下面(不是我用这些名字创建的表!)如果您愿意，我可以简单地将 varchar 字段设置为与 DateTime 类似:

update report1455062507424
set [Move Time] = substring([Move Time], 7, 4) + '-'+ substring([Move Time], 4, 2) + '-'+ substring([Move Time], 1, 2) + ' ' +
substring([Move Time], 12, 5)

小开

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

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

Convert it normally to datetime

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

在你的情况下，你可以

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

小开

Varchar Date Convert to Date and Change the Format

2016年11月12日12:00,21/12/2016,21-12-2016 this Query Works for above to change to this Format dd/MM/yyyy 选择[成员 _ ID ] ，[名称] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

小开

年份测试 > 2079。我发现一个用户在2016年(10/12/2106)输入了2106，而不是2016; 于是在2016年10/12我测试发现 SQL Server 接受了2078，如果年份是2079或更高，就开始抛出这个错误。我还没有做任何进一步的研究，什么样的日期滑动 SQLServer 做。

小开

我最近也遇到过类似的问题。在应用程序和数据库服务器中正确设置了区域设置。然而，SQL 的执行导致

“ varchar 数据类型到 datetime 数据类型的转换导致值超出范围”。

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

要在 SSMS 中检查或更改它，请转到 Security-> Logins 并右键单击运行查询的用户的用户名。选择 properties-> general 并确保对话框底部的默认语言符合您的期望。

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

小开

This error occurred for me because i was trying to store the minimum date and time in a column using inline queries directly from C# code.

在代码中，date 变量被设置为01/01/000112:00:00 AM，因为 C # 中的 DateTime 是用这个日期和时间初始化的，如果没有其他设置的话。MS-SQL 2008日期时间数据类型中允许的最小日期是1753-01-0112:00:00 AM。

我修改了代码中的日期，并将其设置为01/01/1900，没有进一步的错误报告。

小开

我在与 MM 的约会中使用 ToString ()而不是 MM。

小开

你可以利用

Set dateformat <date-format> ;

函数或存储过程来完成任务。

小开

只需确保您的 Dates 是兼容的，或者可以在数据库管理器中正确运行(例如 SQLServerManagementStudio)。例如，日期时间。现在 C # 函数在 SQL 服务器中是无效的，这意味着查询必须包含有效的函数，比如 SQL 服务器的 GETDATE ()。

这个改变对我来说非常有效。

小开

这个问题的原因有点不寻常，但以防万一有人需要它。我正在研究的代码是:

java.text.DateFormat.getDateTimeInstance()

to get a date formatter. The formatting pattern returned by this call changed from Java 8 to Java 9 as described in this bug report: https://bugs.openjdk.java.net/browse/JDK-8152154 apparently the formatting it was returning for me wasn't suitable for the database. The solution was to this instead:

DateTimeFormatter.ISO_LOCAL_DATE_TIME

小开

在顶部添加:

SET DATEFORMAT ymd;

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

小开

我也遇到过类似的问题。我不认为很多人会面临我所面临的情况。尽管如此，我还是想分享我的经历。我在一个测试环境中工作，有日期时间数据被设置为最小日期时间值0001/01/01。此值小于小日期时间数据类型的最小可能数据。因此，一定要检查数据类型的最小最大值。对于小数据时间，你可以参考这个页面: < a href = “ https://Learn.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql? view = sql-server-ver15”rel = “ nofollow norefrer”> https://learn.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15

小开

我也遇到过类似的问题，经过调查，我发现数据库里有很久以前的记录。

因此，我发现从日期到日期时间的转换只适用于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.