----- Pls TRY:create table #T (Ptype int , PNO varchar(50))
insert into #T
select 1,'430422790312881'
insert into #T
select 2,'430422197603128821'select year(getdate())- case Ptype when 1 then cast(('19'+ substring(PNO,7,2)) as int) else cast(substring(PNO,7,4) as int) end
from #Tdrop table #T
insert into #T
select 1,'430422790312881'
insert into #T
select 2,'430422197603128821'select year(getdate())- case Ptype when 1 then cast(('19'+ substring(PNO,7,2)) as int) else cast(substring(PNO,7,4) as int) end
from #Tdrop table #T
你模仿用這一句即可:select year(getdate())- case Ptype when 1 then cast(('19'+ substring(PNO,7,2)) as int) else cast(substring(PNO,7,4) as int) end
from #T
declare @bornYear int
declare @age int
select @CardID='340822198004300016'
select @bornYear=convert(int,substring(@CardID,7,4))
select @age=datepart(yy,getdate())-@bornYear
print @age
a char(15),
b char(18),
id int )
insert test5 values('null','32050219790201001','2')
insert test5 values('320502800112001','null','1')
select isnull(a,0),isnull(b,0),case id when 1 then cast(substring(convert(char(8),getdate(),112),3,2) as int)- cast(substring(a,7,2) as int)+100
else
cast(substring(convert(char(8),getdate(),112),1,4) as int)- cast(substring(b,7,4) as int)
end from test5结果320502800112001 null 23
null 32050219790201001 24