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

I have been getting the following error when running a SQL to convert my data type value from varchar to datetime.

在运行SQL将数据类型值从varchar转换为datetime时，我得到了以下错误。

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

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

I have checked the data and can't see anything to odd: Ran the following checks and all returning no results

我检查了数据，没有发现任何异常:运行以下检查，所有都没有返回结果

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

Is there anything else worth looking at and maybe worth any pointers or help with this issue? Can't seem to get bottom of it.

在这个问题上，还有什么值得关注的，或者有什么建议或者帮助吗?似乎无法弄清真相。

15 个解决方案

#1

I have faced the same problem a week ago. The problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).

一周前我也遇到过同样的问题。问题是时区的设置。指定其他格式，如mm/dd/yyyy(通常有效)。

Specifying the date as 30/12/2013 resulted in the error for me. However, specifying it as mm/dd/yyyy format worked.

指定日期为30/12/2013导致了我的错误。但是，将它指定为mm/dd/yyyy格式是可行的。

If you need to convert your input the you can try looking into the CONVERT method. Syntax is

如果您需要转换您的输入，您可以尝试查看转换方法。语法是

CONVERT(VARCHAR,@your_date_Value,103)

转换(VARCHAR @your_date_Value,103)

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

转换(VARCHAR 12/30/2013,103)

The finishing 103 is the datetime format.

最后的103是datetime格式。

Refer this link for conversion formats and further reading. http://www.w3schools.com/sql/func_convert.asp

请参考此链接以获取转换格式和进一步阅读。http://www.w3schools.com/sql/func_convert.asp

#2

I ran into this issue due to a silly mistake. Make sure the date actually exists!

由于一个愚蠢的错误，我遇到了这个问题。确保日期确实存在!

For example:

例如:

September 31, 2015 does not exist.

2015年9月31日不存在。

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

So this fails with the message:

所以这条信息失败了:

Error converting data type varchar to datetime.

To fix it, input a valid date:

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

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

And it executes just fine.

它执行得很好。

#3

I had similar issue recently. Regional settings were properly setup both in app and database server. However, execution of SQL resulted in

我最近也遇到过类似的问题。区域设置在app和数据库服务器上都设置正确。然而，SQL的执行导致了结果。

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

“将varchar数据类型转换为datetime数据类型会导致值超出范围”。

The problem was the default language of the db user.

问题是db用户的默认语言。

To check or change it in SSMS go to Security -> Logins and right-click the username of the user that runs the queries. Select properties -> general and make sure the default language at the bottom of the dialog is what you expect.

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

Repeat this for all users that run queries.

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

#4

You can make use of

你可以利用

Set dateformat <date-format> ;

in you sp function or stored procedure to get things done.

在您的sp函数或存储过程中完成任务。

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

#6

I've had the same problem and determined that this issue arises because SQL Server does not perform comparisons on characters converted to integers in an identical manner. In my test, I've found that some comparisons of converted characters, such as the exclamation point, will return type conversion errors, while other comparisons of converted characters, such as the space, will be determined to be out of range.

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

This sample code tests the different possible scenarios and presents a solution using nested REPLACE statements. The REPLACE determines if there are any characters in the string that are not numerals or the slash, and, if any exist, the length of the string will be greater than zero, thereby indicating that there are 'bad' characters and the date is invalid.

此示例代码测试不同的可能场景，并使用嵌套替换语句提供解决方案。REPLACE将确定字符串中是否有非数字字符或斜线字符，如果有，字符串的长度将大于0，从而表明有“坏”字符，日期无效。

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:

输出:

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

#7

I too encountered this issue while auto inserting a sysdate to a column.

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

What I did is I changed my system date format to match with SQL server's date format. e.g. my SQL format was mm/dd/yyyy and my system format was set to dd/mm/yyyy. I changed my system format to mm/dd/yyyy and error gone

我所做的就是改变我的系统日期格式以匹配SQL server的日期格式。我的SQL格式是mm/dd/yyy，我的系统格式是dd/mm/yyy。我将系统格式更改为mm/dd/yyyy，错误消失

-kb

#8

As you know this is UK format issue. You can do date conversion indirectly by using function.

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

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

To call this function

调用这个函数

SELECT dbo.ChangeDateFormatFromUK([dates]) from table

Convert it normally to datetime

通常将它转换为datetime

SELECT CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) from table

In your Case, you can do

就你而言，你能做到

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

#9

Test for year > 2079. I found that a user typo'ed 2106 instead of 2016 in the year (10/12/2106) and boom; so on 10/12/2016 I tested and found SQL Server accepted up to 2078, started throwing that error if the year is 2079 or higher. I have not done any further research as to what kind of date sliding SQL Server does.

> 2079年测试。我发现用户输入的日期为2106，而不是2016年(10/12/2106)和boom;因此，在2016年10月12日，我测试并发现SQL Server可以接受到2078，如果年份是2079或更高，就开始抛出这个错误。我还没有对哪种类型的数据滑动SQL Server做进一步的研究。

#10

+ this happens because sql sometimes doesn't recognize dd/mm/yyyy format
+ so we should always check if the input string is a valid date or not and the accordingly convert it to mm/dd/yyyy and so , i have shown below how it can be done, i have created a function to rearrange in mm/dd/yyyy from dd/mm/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

#11

I simply converted the varchar field that I wanted to convert into a new table (with a DateTime filed) to a DateTime compatible layout first and then SQL will do the conversion from varchar to DateTime without problems.

我只是简单地将varchar字段转换为一个新的表(有一个DateTime file)，然后SQL将它转换为一个DateTime兼容的布局，这样就可以毫无问题地从varchar转换为DateTime。

In the below (not my created table with those names !) I simply make the varchar field to be a DateTime lookalike if you want to:

在下面(不是我创建的具有这些名称的表!)我只是让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)

#12

Varchar Date Convert to Date and Change the Format

Nov 12 2016 12:00 , 21/12/2016, 21-12-2016 this Query Works for above to change to this Format dd/MM/yyyy SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

2016年11月12日12:00,21/12/2016,21-12-2016

#13

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.

这个错误发生在我身上，因为我试图使用c#代码中的内联查询将最小日期和时间存储在一个列中。

The date variable was set to 01/01/0001 12:00:00 AM in the code given the fact that DateTime in C# is initialized with this date and time if not set elsewise. And the least possible date allowed in the MS-SQL 2008 datetime datatype is 1753-01-01 12:00:00 AM.

由于c#中的DateTime是用这个日期和时间初始化的，所以在代码中日期变量被设置为01/01/0001 12:00:00。MS-SQL 2008 datetime datetime数据类型中允许的最小日期是1753-01-01 12:00:00。

I changed the date from the code and set it to 01/01/1900 and no errors were reported further.

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

#14

I used ToString() on a date with mm instead of MM.

我使用ToString()表示日期为mm而不是mm。

#15

Just make sure that your Dates are compatible or can be run properly in your database manager(e.g. SQL Server Management Studio). For example, the DateTime.Now C# function is invalid in SQL server meaning your query has to include valid functions like GETDATE() for SQL Server.

请确保您的日期是兼容的，或者可以在数据库管理器中正确运行(例如:SQL Server Management Studio)。例如,DateTime。现在，c#函数在SQL server中无效，这意味着您的查询必须包含有效的函数，如SQL server的GETDATE()。

This change has worked perfectly for me.

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

#1