如何从身份证号码中提取出生日字段,例如:
519003196502020023 --->65/02/02
511002290804151 --->29/08/04
519003660202642 --->66/02/02
SQL语句怎么写哦
519003196502020023 --->65/02/02
511002290804151 --->29/08/04
519003660202642 --->66/02/02
SQL语句怎么写哦
select case when len(num)=18 then convert(datetime,substring(num,7,8))
when len(num)=15 then convert(datetime,'19'+substring(num,7,6))
end as birthday
from T
from tablename
select case len(num) when 18 then substring(num,9,2)+'/'+substring(num,11,2)+'/'+substring(num,13,2)
when 15 then substring(num,7,2)+'/'+substring(num,9,2)+'/'+substring(num,11,2)
end as birthday
from T
(
identityNum varchar(18)
)
insert into @t
select '519003196502020023' union all
select '511002290804151' union all
select '519003660202642'
select * from @tupdate @t set identityNum=(case when len(identityNum)=18 then convert(varchar(10),convert(datetime,substring(identityNum,7,8)),11)
when len(identityNum)=15 then convert(varchar(10),convert(datetime,'19'+substring(identityNum,7,6)),11)
end)select * from @t
select case when len(num)=18 then convert(datetime,substring(num,7,8))
when len(num)=15 then convert(datetime,'19'+substring(num,7,6))
end as birthday
from T