mysql通过生日计算年龄

最新推荐文章于 2024-06-07 14:16:07 发布

janexu12

最新推荐文章于 2024-06-07 14:16:07 发布

阅读量153

点赞数 2

文章标签： sql 数据库

本文链接：https://blog.csdn.net/janexu12/article/details/138244068

版权

mysql通过生日计算年龄
– 生日类型是String yyyy-mm-dd 按一年365天计算年龄

SELECT
  birthday,
  FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m%d'), DATE_FORMAT(birthday, '%Y%m%d'))/365) AS age
FROM
  persons;

– 生日类型是Date

SELECT
  birthday,
  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age
FROM
  persons;

– 生日类型是String yyyy-mm 按一年12个月计算年龄

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));