select * from tb where cast(substring(15身份证号,开始,8) as datetime)<'19830101' or cast(substring(18身份证号,开始,8) as datetime)<'19830101'
select * from ta where (case len(sfz) when 15 then '19'+substring(7,6) else substring(7,8)) > '19830101'
DECLARE @t TABLE(num VARCHAR(20)) INSERT @t SELECT '61232219850101xxxx' UNION ALL SELECT '430802870101xxx' UNION ALL SELECT '61232219800101xxxx' UNION ALL SELECT '430802770101xxx'SELECT * FROM @t WHERE DATEDIFF(dd,LEFT(CASE WHEN LEN(num)=18 THEN '' ELSE '19' END + STUFF(num,1,6,''),8),'19830101')>0
SELECT IdentityID FROM TbTemp WHERE SUBSTRING('IdentityID', LEN('IdentityID')-9, 6)
Create table sf_talbe (sf varchar(30)) insert into sf_talbe select '610502196301013836' insert into sf_talbe select '610502198509153836' insert into sf_talbe select '61050219820915' insert into sf_talbe select '610502198509153836' insert into sf_talbe select '61050219520915' select substring(sf,7,8) from sf_talbe where (len(sf)>=18 ) and Convert(int,substring(sf,7,8))>19830101 union all select substring(sf,7,8) from sf_talbe where (len(sf)=15 ) and Convert(int,substring(sf,7,8))>19830101
declare @tb table([pid] varchar(20),[pname]varchar(10)) insert into @tb select '62242919840321191x','aa' union select '622429800101191','ff' union select '622429195401011611','dfd' union select '622429460102232','afdf' union select '622429198301011910','fad'select * from @tb where case when len(rtrim(pid))=15 then cast(('19'+substring(pid,7,6)) as datetime) when len(rtrim(pid))=18 then cast((substring(pid,7,8)) as datetime) end <'19830101'(所影响的行数为 5 行)pid pname -------------------- ---------- 622429195401011611 dfd 622429460102232 afdf 622429800101191 ff
楼主可以贴一下射份证编号说明就可以了。61232219850101xxxx
430802870101xxx这样的吗?15位,从第7位开始 yyMMdd
18位,从第7位开始 yyyyMMdd
?
from tb
where cast(substring(15身份证号,开始,8) as datetime)<'19830101'
or cast(substring(18身份证号,开始,8) as datetime)<'19830101'
from ta
where (case len(sfz) when 15 then '19'+substring(7,6) else substring(7,8)) > '19830101'
INSERT @t SELECT '61232219850101xxxx'
UNION ALL SELECT '430802870101xxx'
UNION ALL SELECT '61232219800101xxxx'
UNION ALL SELECT '430802770101xxx'SELECT * FROM @t
WHERE DATEDIFF(dd,LEFT(CASE WHEN LEN(num)=18 THEN '' ELSE '19' END + STUFF(num,1,6,''),8),'19830101')>0
SELECT IdentityID FROM TbTemp
WHERE SUBSTRING('IdentityID', LEN('IdentityID')-9, 6)
Create table sf_talbe
(sf varchar(30))
insert into sf_talbe select '610502196301013836'
insert into sf_talbe select '610502198509153836'
insert into sf_talbe select '61050219820915'
insert into sf_talbe select '610502198509153836'
insert into sf_talbe select '61050219520915'
select substring(sf,7,8) from sf_talbe
where (len(sf)>=18 ) and Convert(int,substring(sf,7,8))>19830101 union all
select substring(sf,7,8) from sf_talbe
where (len(sf)=15 ) and Convert(int,substring(sf,7,8))>19830101
insert into @tb
select '62242919840321191x','aa'
union
select '622429800101191','ff'
union
select '622429195401011611','dfd'
union
select '622429460102232','afdf'
union
select '622429198301011910','fad'select *
from @tb
where
case
when len(rtrim(pid))=15 then cast(('19'+substring(pid,7,6)) as datetime)
when len(rtrim(pid))=18 then cast((substring(pid,7,8)) as datetime)
end
<'19830101'(所影响的行数为 5 行)pid pname
-------------------- ----------
622429195401011611 dfd
622429460102232 afdf
622429800101191 ff
上面的数字是对declare影响的,
下面的是select影响的。