[求助]如何筛选生日
如何从16位和18位身份证号码中筛选出当天过生日的网友,先谢谢各位了
create table #people
(name varchar(20),sfz varchar(20))
insert #people select 'a','123456198508193717'
union select 'a','1234568512053717'
union select 'b', '111111111112053124'
select * from #people where ( substring(sfz,11,4)=
substring(convert(varchar(11),getdate(),110),1,2)+substring(convert(varchar(11),getdate(),110),4,2)
and len(sfz)=18 ) or ( substring(sfz,9,4)=
substring(convert(varchar(11),getdate(),110),1,2)+substring(convert(varchar(11),getdate(),110),4,2)
and len(sfz)=16 )
结果
name sfz
-------------------- --------------------
b 111111111112053124
a 1234568512053717
(所影响的行数为 2 行)