Jiniya

WEB

MSSQL- 주민등록번호 앞자리로 나이 구하기

주민번호 필드 jumin : 020801-*******

나이 구한 후 5세 이하의 DB만 출력

 

 

SELECT *
FROM (
  select *,year(getdate()) -
    convert(smallint, case when substring(jumin,8,1) in ('1','2') then '19'
              when substring(jumin,8,1) in ('3','4') then '20'
           when substring(jumin,8,1) in ('9','0') then '18'
         end + left(jumin,2)) -
   case when substring(jumin,3,4) >= substring(convert(char(10),getdate(),112),5,4)
     then 1 else 0
   end
 as age 
 FROM member
) A
WHERE age <= 5