SQL Server根据身份证号字段修改生日字段

深碍√TFBOYSˉ_ 2022-04-12 11:57 576阅读 0赞

## **身份证分为18位和15位，对应的日期格式分别为** ##

**18位身份证：7-13位yyyyMMdd**，  
**15位身份证：7-11位yyMMdd**。

今天由于个人的失误，得到一个深刻的教训，刻苦铭心，以后绝不再犯。

深刻反思（update，delete 一定不要忘了加where）：  
![错误][20181203120256422.png]

虽然本地数据库有备份的话可以回滚，不过以防万一，本地测试数据库一般，不会保存那么勤，多个人同时用，你会滚难免会出现异常，特别是对于一个实习生，还是谨慎为好，自己写了几个表测试之后，就对数据库进行修复工作了。。。自己的错还要自己来啊。

大致sql代码如下：

--对18位身份证日期进行修改,大范围修改,2月29日的可能有错,需要下面的查询语句人工检查
    	update kp_user_info 
    	set birthday=
    	case when substring(idCard,7,4)<'1753' 
    	then null
    	when SUBSTRING(idCard,11,2)='00' or SUBSTRING(idCard,11,2)>'12'
    	then null 
    	when SUBSTRING(idCard,13,2)='00' or SUBSTRING(idCard,13,2)>'31'
    	then null 
    	else cast(substring(idCard,7,4)+'-'+SUBSTRING(idCard,11,2)+'-'+SUBSTRING(idCard,13,2)+' 00:00:00.000' as date)
    	end
    	where LEN(idCard)=18 and ID>=10000 
    	
    --对15位身份证日期进行修改,大范围修改,2月29日的可能有错,需要下面的查询语句人工检查
    	
    	update kp_user_info 
    	set birthday=
    	case when SUBSTRING(idCard,9,2)='00' or SUBSTRING(idCard,9,2)>'12'
    	then null 
    	when SUBSTRING(idCard,11,2)='00' or SUBSTRING(idCard,11,2)>'31'
    	then null 
    	else cast('19'+substring(idCard,7,2)+'-'+SUBSTRING(idCard,9,2)+'-'+SUBSTRING(idCard,11,2)+' 00:00:00.000' as date)
    	end
    	where LEN(idCard)=15 and ID>=233735 and ID<233734
    	
    	
    	
    --对某范围进行查询,查看错误类型
    
    select * from kp_user_info where LEN(idCard)=15 and ID>=65220 and ID<65250
    
    --针对0229生日的进行查看是否是闰年,是否会转换错误 
    --7501 65228 70256 213519 233734 这些id身份证信息错误
    select * from kp_user_info where LEN(idCard)=15 and SUBSTRING(idCard,9,4)='0229' and ID>=231000
    	
    --暂时把生日修改为空
    update kp_user_info set birthday=null where ID in(7501,65228,70256,213519,233734)

不是瑞年的话，或者日期转换错误，数据库会自动回滚。所以需要把错误数据跳过才可以修改转换正常的数据，由于判断条件太多，时间比较急，粗糙的使用了这个方法，快速修复生日。  
开始想全部update成正确的出现的错误：  
![错误提示][20181203120840551.jpg]  
本地测试库总有错误的身份证。所以，只能自己绕过了。  
绕过之后：  
![绕过修改][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3F1YWludF9jc2Ru_size_16_color_FFFFFF_t_70]

[20181203120256422.png]: /images/20220412/503b8100c8994f438063f0ec2f5d1e9a.png
[20181203120840551.jpg]: /images/20220412/9c801ee500da4ad8b43a027d8c411cfe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3F1YWludF9jc2Ru_size_16_color_FFFFFF_t_70]: /images/20220412/681533170ef849afb6eb90d56c13d9b3.png