【SQL开源代码栏目提醒】:网学会员鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“CheckDate.sql”一文,供大家参考学习
CREATE FUNCTION CheckDate
(
@ChineseDate Char(8)
)
RETURNS int
AS
BEGIN
/*定义累计错误次数*/
DECLARE @iERR AS int
/*初始值为0*/
SELECT @iERR = 0
/*先将日期的前后空格清空*/
SET @ChineseDate = RTRIM(LTRIM(@ChineseDate))
/*假如是空的, 则错误数+1*/
IF @ChineseDate = ''
BEGIN
SET @iErr = @iErr + 1
END
/*假如字符串的长度不等于8, 则错误数+1*/
IF LEN(@ChineseDate) <> 8
BEGIN
SET @iErr = @iErr + 1
END
/*年度必须在01-99间*/
IF (LEFT(@ChineseDate, 2) < '01') OR
(LEFT(@ChineseDate, 2) > '99')
BEGIN
SET @iErr = @iErr + 1
END
/*月份必须在01-12间*/
IF (SUBSTRING(@ChineseDate, 4, 2) < '01') OR
(SUBSTRING(@ChineseDate, 4, 2) > '12')
BEGIN
SET @iErr = @iErr + 1
END
/*日期必须在01-31间*/
IF (RIGHT(@ChineseDate, 2) < '01') OR
(RIGHT(@ChineseDate, 2) > '31')
BEGIN
SET @iErr = @iErr + 1
END
/*年度, 月份, 日期中间是用-隔开的*/
IF (SUBSTRING(@ChineseDate, 3, 1) <> '-') OR
(SUBSTRING(@ChineseDate, 6, 1) <> '-')
BEGIN
SET @iErr = @iErr + 1
END
/*当以上皆没错误时, 再做进一步检查
是为了避免下面的
程序CAST会发生错误*/
IF (@iErr = 0)
BEGIN
/*再次验证月份是否在1-12间*/
IF (CAST(SUBSTRING(@ChineseDate, 4, 2) AS int) < 1) OR
(CAST(SUBSTRING(@ChineseDate, 4, 2) AS int) > 12)
BEGIN
SET @iErr = @iErr + 1
END
/*再次验证日期是否在1-31间*/
IF (CAST(SUBSTRING(@ChineseDate, 7, 2) AS int) < 1) OR
(CAST(SUBSTRING(@ChineseDate, 7, 2) AS int) > 31)
BEGIN
SET @iErr = @iErr + 1
END
/*若月份属于小月, 则再判断日期是否不超过30*/
IF (CAST(SUBSTRING(@ChineseDate, 4, 2) AS int) IN (2, 4, 6, 9, 11))
BEGIN
IF (CAST(SUBSTRING(@ChineseDate, 7, 2) AS int) > 30)
BEGIN
SET @iErr = @iErr + 1
END
END
ELSE
BEGIN
/*若月份属于大月, 则日期不可超过31*/
IF (CAST(SUBSTRING(@ChineseDate, 7, 2) AS int) > 31)
BEGIN
SET @iErr = @iErr + 1
END
END
/*如果是2月份, 还要判断是否为闰年*/
IF (CAST(SUBSTRING(@ChineseDate, 4, 2) AS int) = 2)
BEGIN
/*先判断日期是否大于29*/
IF (CAST(SUBSTRING(@ChineseDate, 7, 2) AS int) > 29)
BEGIN
SET @iErr = @iErr + 1
END
ELSE
BEGIN
IF (NOT (CAST(SUBSTRING(@ChineseDate, 1, 2) AS int) IN
(1, 5, 9, 13, 17, 21, 25, 29, 33, 37, 41, 45,
49, 53, 57, 61, 65, 69, 73, 77, 81, 85, 89, 93, 97))) AND
(CAST(SUBSTRING(@ChineseDate, 7, 2) AS int) > 28)
BEGIN
/*是闰年, 但日期大于28*/
SET @iErr = @iErr + 1
END
END
END
END
RETURN @iERR
END