对身份证日期以及身份证对应的性别字段进行错误检查

SQLServer State：
1、对身份证日期(年月日）错误进行检查

select  cbfbm,cyzjhm,cyxm ,Substring(cyzjhm,7,4)+'-'+Substring(cyzjhm,11,2)+'-'+Substring(cyzjhm,13,2)
from cbf_jtcy
where (len(cyzjhm)<>15 and len(cyzjhm)<>18)
or (Substring(cyzjhm ,7,2)<19)
or (Substring(cyzjhm ,7,2)>20)
or (Substring(cyzjhm ,11,2)>12)
or (Substring(cyzjhm ,11,2) in (01,03,05,07,08,10,12) and Substring(cyzjhm ,13,2)>31)
or (Substring(cyzjhm ,11,2) in (04,06,09,11) and Substring(cyzjhm ,13,2)>30)
or ((Substring(cyzjhm,7,4) % 4=0 and Substring(cyzjhm,7,4) % 100<>0 or Substring(cyzjhm,7,4) % 400=0) and Substring(cyzjhm ,11,2)=2 and Substring(cyzjhm ,13,2)>29)
or ((Substring(cyzjhm,7,4) % 4<>0 and Substring(cyzjhm,7,4) % 100=0 or Substring(cyzjhm,7,4) % 400<>0) and Substring(cyzjhm ,11,2)=2 and Substring(cyzjhm ,13,2)>28)

2、身份证对应性别错误进行检查

select *
FROM CBF_JTCY
where (((len(CYZJHM)=15) and (Substring(CYZJHM,15,1) in (1,3,5,7,9)) and CYXB<>'1')
or ((len(CYZJHM)=15) and (Substring(CYZJHM,15,1) in (2,4,6,8,0)) and CYXB<>'2'))
or (((len(CYZJHM)=18) and (Substring(CYZJHM,17,1) in (1,3,5,7,9)) and CYXB<>'1')
or ((len(CYZJHM)=18) and (Substring(CYZJHM,17,1) in (2,4,6,8,0)) and CYXB<>'2'))

 注：如果在Access数据库使用该SQL语句，只需将Substring函数改成mid函数，其余不变

转载于:https://www.cnblogs.com/ParanoiaApe/p/7885088.html