SQL 将 nvarchar 数据类型转换为 datetime 数据类型导致值超出范围 - 闰年
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14983066/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value - Leap Year
提问by user2091766
I get this error in stored procedure code and I discovered one of the records is from a leap year date 2008-02-29. How do I get around this with the code I have. Here is the code:
我在存储过程代码中收到此错误，我发现其中一条记录来自闰年日期 2008-02-29。我如何使用我拥有的代码解决这个问题。这是代码：
set @ContractDate = cast(cast(@Year as nvarchar(4))
+ '/' + cast(datepart(mm,@ContractDate) as nvarchar(2))
+ '/' + cast(datepart(dd,@ContractDate) as nvarchar(2)) as datetime)
回答by gbn
First off:
首先：
- What values do you have for @Year and @ContractDate?
- And what format/type is @ContractDate?
- @Year 和 @ContractDate 有什么值？
- @ContractDate 是什么格式/类型？
I ask because this works for me on us_englishSQL Server 2012
我问是因为这在us_englishSQL Server 2012上对我有用
SELECT cast('2008/02/29' as datetime)
However, if I change SET DATEFORMATthen I can break the above code
但是，如果我改变，SET DATEFORMAT那么我可以打破上面的代码
-- breaks
SET DATEFORMAT DMY;
SELECT cast('2008/02/29' as datetime)
Using ISO date format is OK no matterwhat I use for SET DATEFORMAT
无论我使用什么 SET DATEFORMAT都可以使用 ISO 日期格式
SET DATEFORMAT DMY;
SELECT cast('20080229' as datetime);
SET DATEFORMAT MDY;
SELECT cast('20080229' as datetime);
SET DATEFORMAT YDM;
SELECT cast('20080229' as datetime);
Conclusion: you are using non ISO date formats and relying on implicit conversions
结论：您使用的是非 ISO 日期格式并依赖于隐式转换
回答by Kaf
It is better to get the string in ISO format (yyyymmdd) before converting to datetime/date and it will work in any server regardless of culture settings
最好yyyymmdd在转换为日期时间/日期之前以 ISO 格式 ( )获取字符串，无论文化设置如何，它都可以在任何服务器上工作
If @ContractDateis a datetime/datetype and @yearis like 2012/'2012'then following should work. Working Example
如果@ContractDate是一种datetime/date类型并且@year是这样的，2012/'2012'那么以下应该有效。工作示例
Set @ContractDate = convert(datetime,
convert(varchar(4),@year) + right(convert(varchar(8),@ContractDate,112),4))
回答by Ramkumar
ALTER PROCEDURE [dbo].[Sden_report1]
@fromdt nvarchar(13),
@todt nvarchar(13)
AS
BEGIN
select AD_PID,AD_Appointmentdate,AD_Patientname,AD_Patientphno,AD_Visitpurpose
from Appointment_Details1
where
--PL_CID = @CID and PL_Itemcode = @JCode AND
(@fromdt = '' OR (DATEDIFF(DAY,AD_Appointmentdate, @fromdt) <= 0))
AND (@todt = '' OR (DATEDIFF(DAY,AD_Appointmentdate, @todt) >= 0))
end
GO
When i Execute the procedure
i will get below error:
EXEC Sden_report1 '20-11-2012','20-05-2013'
Error
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Anyone Help...`enter code here`
