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'

2 月有点棘手.逐步开始，找出所有超过 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'

然后您需要对 2 月日期大于 28 且不是闰年的其余行重复此操作.在过去的 100 年中，任何能被 4 整除的年份都是闰年，因此您可以以此识别余数(假设您已更正/删除了已找到的错误 2 月条目):

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屋！