SELECT
机构名称,卫生机构类型代码,姓名,出生日期,
(substring(now(),1,4)-substring(`身份证件号码`,7,4))-(substring(`身份证件号码`,11,4)-date_format(now(),'%m%d')>0) as '年龄',`编制情况`
FROM
`人力表 (卫生人力)`
WHERE
((substring(now(),1,4)-substring(`身份证件号码`,7,4))-(substring(`身份证件号码`,11,4)-date_format(now(),'%m%d')>0) >'60')AND(`编制情况` != '4')
CASE
WHEN LENGTH(`身份证号`)=18 THEN YEAR (now()) - YEAR (substring(`身份证号`, 7, 8))
WHEN LENGTH(`身份证号`)=15 THEN YEAR (now()) - YEAR(STR_TO_DATE(CONCAT('19',substring(`身份证号`, 7, 2)),'%Y'))
ELSE NULL
END AS AGE
excel
-
15位用如下函数:
=(TODAY()-DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)))/365
-
18位使用如何函数:
=(TODAY()-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))/365
-
综合运用。
如果增加判断函数,可使用如下函数:
=(TODAY()-IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))))/365
年龄分组
=IF(L4<5,"0-4",IF(L4<10,"5-9",IF(--RIGHT(L4,1)<5,LEFT(L4,1)&0&"-"&LEFT(L4,1)&4,LEFT(L4,1)&5&"-"&LEFT(L4,1)&9)))