sql 身份证号码提取出生日期

Question

手上有一张表，表里含有2个字段，一个是出生年月（birth），数据类型为datetime，一个是身份证号码（idcard），数据类型为varchar，，现在出生年月这个字段里面是空的（null），我现在想把身份证号里面的出生日期提取出来，放进出生年月这个字段里，但是不知道该如何写语句，这里面还隐含一个条件，因为身份证号码分15位和18位两种。还要把空字段继续保留为NULL
如何在SQL数据库中直接搞定》？我导入数据后，系统把标记为NULL的出生年月字段都改为了1900-1-1，如何在数据库中直接提取出生日期，写入BIRTH字段中，把标记为1900-1-1的字段也根据身份证号码提取更改

Answer 1

工具/材料：Management Studio。 

1、首先在桌面上，点击“Management Studio”图标。 

2、之后在Management Studio窗口中，点击左上角“新建查询”选项。 

3、接着在Management Studio窗口中，输入提取身份证号码中出生日期的sql语句“select name as 姓名,substring(cardid,7,8) as 出生日期 from test1”。 

4、然后在Management Studio窗口中，点击左上方“执行”按钮。 

5、最后在Management Studio窗口中，显示身份证号码中出生日期提取成功。

Answer 2

15位身份证号码和18位身份证号码都是第7到第14位为生日信息，这不是问题

update 表名 set birth=substring(idcard,7,14) where birth='1900-1-1';

---
以上，希望对你有所帮助。本回答被提问者采纳

Answer 3

if exists (select * from sysobjects where name='f_getbirth' and xtype='FN')
drop function f_getbirth
go

create function f_getbirth (@card varchar(20))
returns datetime
as
begin
declare @year varchar(4),@month varchar(2),@day varchar(2),@len int,@birth datetime
select @len=len(@card)
if @len=18
begin
select @year=substring(@card,7,4)
select @month=substring(@card,11,2)
select @day=substring(@card,13,2)
end
if @len=15
begin
select @year=substring(@card,7,2)
if cast(@year as int)>9
select @year='19'+@year
else
select @year='20'+@year
select @month=substring(@card,9,2)
select @day=substring(@card,11,2)
end
select @birth=cast(@year+'-'+@month+'-'+@day as datetime)
return @birth
end

update 表 set birth=dbo.f_getbirth(idcard) where idcard is not null

我这个函数在15位的时候只是把年号大于09的当成20世纪的了，小于09的当成21世纪的了，不知道能不能满足你的要求

以上，希望对你有所帮助！

Answer 4

update 表 set birth=case DATALENGTH(idcard)
when 18 then SUBSTRING(idcard,7,8)
when 15 then '19'+SUBSTRING(idcard,7,6)--2000年以后的都18位
else null
end

自己测试了一下,发现datalength()函数只适合单字节字符,对于双字节字符则会翻倍,所以改用len()函数,并去掉前导空格,修改后如下:
update 表 set birth=case len(ltrim(idcard))
when 18 then SUBSTRING(idcard,7,8)
when 15 then '19'+SUBSTRING(idcard,7,6)--2000年以后的都18位
else null
end

Answer 5

15位和18位有点不同，15位的提取出来的出身年月的年份不带19或者20，也就是类似860122这样的格式，18位的提取出来的出身年月是类似19860122这样的格式
所以说提取的长度不同

最好是写个存储过程，先判断出生年月字段的数据的位数，再分别处理