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

一周前我遇到了同样的问题。问题在于时区设置。以其他格式指定，例如 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

---

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

例如：

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

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

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

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

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

---

你可以利用

Set dateformat <date-format> ;

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

---

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

---

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

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

---

发生这种情况是因为 sql 有时无法识别 dd/mm/yyyy 格式。因此，我们应该始终检查输入字符串是否为有效日期，并相应地将其转换为 mm/dd/yyyy 等，我在下面展示了它是如何完成的，我创建了一个函数以在 mm/dd/yyyy 中重新排列来自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

---

我遇到了同样的问题，并确定出现此问题是因为 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

---

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

我所做的是更改了系统日期格式以匹配 SQL 服务器的日期格式。例如，我的 SQL 格式是 mm/dd/yyyy，而我的系统格式设置为 dd/mm/yyyy。我将系统格式更改为 mm/dd/yyyy 并且错误消失了

-kb

---

测试年份 > 2079。我发现用户在当年（2106 年 10 月 12 日）输入了 2106 而不是 2016 并且繁荣；所以在 2016 年 10 月 12 日，我测试并发现 SQL Server 接受到 2078，如果年份是 2079 或更高，则开始抛出该错误。我还没有对 SQL Server 的日期滑动类型进行任何进一步的研究。

---

我只是先将想要转换为新表（带有 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)  

---

Varchar Date Convert to Date and Change the Format

2016 年 11 月 12 日 12:00 , 21/12/2016, 21-12-2016 此查询适用于上述更改为此格式 dd/MM/yyyy SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

---

在顶部添加：

SET DATEFORMAT ymd; 

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

---

我有类似的问题，经过调查，我发现数据库中有非常旧的日期。

所以我发现从日期转换为日期时间仅适用于 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.

---

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

代码中的日期变量设置为 01/01/0001 12:00:00 AM，因为 C# 中的 DateTime 如果未设置，则使用此日期和时间进行初始化。 MS-SQL 2008 日期时间数据类型中允许的最短日期是 1753-01-01 12:00:00 AM。

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

---

我在与 mm 而不是 MM 的日期上使用了 ToString()。

---

只需确保您的日期兼容或可以在您的数据库管理器（例如 SQL Server Management Studio）中正确运行。例如，DateTime.Now C# 函数在 SQL Server 中无效，这意味着您的查询必须包含有效的函数，例如 SQL Server 的 GETDATE()。

这种变化对我来说非常有效。

---

这个问题的原因有点不寻常，但以防万一有人需要它。我正在处理的代码正在使用：

java.text.DateFormat.getDateTimeInstance()

获取日期格式化程序。此调用返回的格式模式从 Java 8 更改为 Java 9，如本错误报告中所述：https://bugs.openjdk.java.net/browse/JDK-8152154 显然它为我返回的格式不适合数据库。解决方案是：

DateTimeFormatter.ISO_LOCAL_DATE_TIME

---

我遇到了类似的问题。我不认为很多人会面临我所面临的情况。尽管如此，我还是想分享我的经验。我在测试环境中工作，并且有设置为最小日期时间值 0001/01/01 的日期时间数据。该值小于 smalldatetime 数据类型的最小可能数据。因此，请务必检查数据类型的 Min Max 值。对于 smalldatetime，您可以参考此页面：https://docs.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15

---