SQL 中的 IsDate 函数将无效日期评估为有效 [英] IsDate Function in SQL evaluates invalid dates as valid
问题描述
我正在针对从 Excel 文件导入的数据运行 SQL 语句.在此 SQL 中,我正在检查用户是否使用 IsDate 函数正确输入了日期.由于这是尚未转换的原始数据,因此所有日期都存储在 varchar 数据类型字段中.
I am running a SQL Statement against imported data from Excel Files. In this SQL I am checking if the users have entered dates properly by using IsDate function. Since this is a raw data that hasn't been converted yet, all dates are stored in a varchar data type field.
在某些情况下,当用户输入的日期格式明显不正确时,IsDate 会返回 1(有效日期).
In some circumstances IsDate returns 1 (valid date) when there is clearly an incorrect date format entered by the user.
例如:
07/001/2012
2012-07-002
007/002/2012
关于如何处理这个问题有什么建议吗?
Any Suggestions on how to handle this problem?
SELECT *
FROM tblImport
WHERE (ISDATE(dt) = 0
AND (dt is not null AND dt <> ''))
谢谢!
附言殴打用户没有帮助.
p.s. Smacking users' did not help.
推荐答案
我做了很多数据转换工作,这是我创建的一个函数,我几乎每天都使用它来清除错误日期:
I do a lot of data conversion work and here is a function that I created and use it practically everyday to weed out the bad dates:
CREATE FUNCTION dbo.fnCheckDate
(@InDate nvarchar(50))
RETURNS DATETIME
AS
BEGIN
declare @Return DATETIME
select @return = CASE WHEN ISDATE(@InDate) = 1
THEN CASE WHEN CAST(@InDate as DATETIME) BETWEEN '1/1/1901 12:00:00 AM' AND '6/6/2079 12:00:00 AM'
THEN @InDate
ELSE null
END
ELSE null
END
return @return
END
GO
结果:
SELECT dbo.fnCheckDate('07/001/2012') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('2012-07-002') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('007/002/2012') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('00/002/2012') --> Returns Null
SELECT dbo.fnCheckDate('006/031/2012') --> Returns Null
SELECT dbo.fnCheckDate('') --> Returns Null
这篇关于SQL 中的 IsDate 函数将无效日期评估为有效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!