mysql通过生日计算年龄
mysql通过生日计算年龄(同理可得通过18位身份证号计算年龄)
– 生日类型是String yyyy-mm-dd 按一年365天计算年龄
1 2 3 4 5 | SELECT
birthday,
FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m%d'), DATE_FORMAT(birthday, '%Y%m%d'))/365) AS age
FROM
persons;
|
– 生日类型是Date
1 2 3 4 5 | SELECT
birthday,
TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age
FROM
persons;
|
– 生日类型是String yyyy-mm 按一年12个月计算年龄
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT
m.sex,
m.card_no,
m.birth_date,
FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(CONCAT(m.birth_date,'-01'), '%Y%m'))/12) AS age
FROM
tb_cloud_member m
LEFT JOIN tb_cloud_laborun l on l.laborun_code=m.laborun_code
LEFT JOIN tb_cloud_enterprises tce ON tce.id = m.unit_id
and l.is_deleted=0
and tce.is_deleted=0
where
1=1
and m.is_deleted=0
and l.is_laborun =1
and m.is_new_occupation=2
AND ( m.retirement_flag IS NULL OR m.retirement_flag = '' OR m.retirement_flag = '0' OR m.retirement_flag = '2' )
AND ( m.job_status IS NULL OR m.job_status not in (4,5,6));
|
