SQL中的有效日期验证 [英] Valid date verification in SQL

问题描述

我有一个列，其格式为（ yyyymmdd ）的 LossDate 。我需要验证如果该月在30天内结束，那么没有报告损失日期为31的损失。我有数百万条记录。帮助将不胜感激。

I have a column that holds LossDate in the format of (yyyymmdd). I need to verify that if the month ends in 30 days, then no loss was reported with a loss date of 31. I have millions of records. Help will be highly appreciated.

LossDate
--------
20120128
20150520
20180631

查询应返回最后一个无效记录，因为200806在30天内结束。

Query should return the last invalid record because 200806 ends in 30 days.

推荐答案

对于您的特定问题，以下内容将返回错误的行：

For your specific question, the following will return bad rows:

select * 
from table 
where substr(lossdate,5,2) in ('04','06','09','11')
    and substr(lossdate,7,2) > '30'

根据您的输入界面，您可能需要重复31个月，显示值大于31。

Depending on your input interface, you may need to repeat that for months with 31 days that show values greater than 31.

select * 
from table 
where substr(lossdate,5,2) in ('01','03','07','08','10','12')  
    and substr(lossdate,7,2) > '31'

Feb有点棘手。逐步开始，确定所有29天内的2月条目，您可以立即删除/修复这些条目。

Feb is a little trickier. Start incrementally, identifying all February entries with more than 29 days, you can get rid of/fix those immediately.

select * 
from table 
where substr(lossdate,5,2) = '02' 
    and substr(lossdate,7,2) > '29'

然后，您需要对其余日期（Feb日期大于28和aren）重复此操作不是a年。在过去的100年中，任何可以被4整除的年份都是a年，因此您可以用此来识别余数（假设您更正/删除了已经找到的不良Feb条目）：

Then you need to repeat this for the remaining rows with Feb dates greater than 28 and aren't a leap year. In the last 100 years, any year divisible by 4 is a leap year, so you can identify the remainder with this (assuming you corrected/deleted the bad Feb entries you already found):

select * 
from table 
where substr(lossdate,5,2) = '02' 
    and mod(substr(lossdate,1,4),4)) <> 0 
    and substr(lossdate,7,2) > '28' 

这篇关于SQL中的有效日期验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！