使用时期函数根据出生日期计算出最近的生日时间

本文详细介绍MySQL中日期函数的使用，包括date_add、date_sub和date_format，并通过实例演示如何利用这些函数计算员工的最近生日。从数据准备到SQL语句实现，逐步解析计算流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 日期函数:

date_add(date,INTERVAL expr unit) 日期增加
date_sub(date,INTERVAL expr unit) 日期减少
date_format(date,日期格式化表达式) 日期格式化

2. 数据准备和思路编写,sql实现 :

2.1. 首先数据准备中employees是mysql官网的示例数据库,地址如下:https://launchpad.net/test-db/employees-db-1/1.0.6,进入网址找到如下位置进行下载:

在这里插入图片描述

我选择的是第二个下载,之后解压,如下图:

在这里插入图片描述

2.2. 找到employees.sql文件打开进行修改,下图中的内容都要注释掉:

在这里插入图片描述
如果不注释会出现storage_engine,具体原因也是不清楚可能是版本的问题吧,原谅我不是个执着的人,我要学习sql语句,而不是修理工!!!

2.3. 之后进行保存,如下操作导入数据库:

打开cmd命令行，输入以下命令：
cd 你的employees解压所在的目录(建议路径短一些,我的: D:\employees_db)
mysql -t -u root -p < employees.sql
输入密码

完成，可以使用了，至此employee数据安装完成,进行下面的操作:

-- 数据准备:
CREATE TABLE employees LIKE employees.employees;
INSERT INTO employees
SELECT * FROM employees.employees LIMIT 0,10;
INSERT INTO employees
SELECT 10011,'1972-02-29','Jiang','David','M','1990-2-20'
-- 通过出生日期和当前时间计算用户最近的生日步骤:
-- 1:先通过当前时间和出生日期计算出年数差
-- 2:再通过date_add函数计算出当前的生日日期和下一次的生日日期
-- 3:之后在判断是否是闰月的情况
-- 4:最后判断生日是否已经过去,如果没有即是当前年,否则是下一年

-- 步骤 1
SELECT
	CONCAT( last_name, "", first_name ) AS 'name',
	birth_date AS 'birthday',
	( YEAR ( SYSDATE( ) ) - YEAR ( birth_date ) ) AS 'diff',
	SYSDATE( ) AS 'today'
FROM
	employees;
-- 步骤 2	
SELECT 
	name,
	birthday,
	DATE_ADD( birthday, INTERVAL diff YEAR ) AS 'nearBirthday',
	DATE_ADD( birthday, INTERVAL diff + 1 YEAR ) AS 'nextBirthday',
	today
FROM
	(
SELECT
	CONCAT( last_name, "", first_name ) AS 'name',
	birth_date AS 'birthday',
	( YEAR ( SYSDATE( ) ) - YEAR ( birth_date ) ) AS 'diff',
	SYSDATE( ) AS 'today' 
FROM
	employees 
	) AS temp_a;
-- 步骤 3
SELECT
	name,
	birthday,
	DATE_ADD( nearBirthday, INTERVAL IF ( DAY ( birthday ) = 29 && DAY ( nearBirthday ) = 28, 1, 0 ) DAY) AS current,
	DATE_ADD( nextBirthday, INTERVAL IF ( DAY ( birthday ) = 29 && DAY ( nextBirthday ) = 28, 1, 0 ) DAY) AS next ,
	today
FROM
	(
SELECT
	name,
	birthday,
	DATE_ADD( birthday, INTERVAL diff YEAR ) AS 'nearBirthday',
	DATE_ADD( birthday, INTERVAL diff + 1 YEAR ) AS 'nextBirthday',
	today 
FROM
	(
SELECT
	CONCAT( last_name, "", first_name ) AS 'name',
	birth_date AS 'birthday',
	( YEAR ( SYSDATE( ) ) - YEAR ( birth_date ) ) AS 'diff',
	SYSDATE( ) AS 'today' 
FROM
	employees 
	) AS temp_a 
	) AS temp_b;
-- 步骤 4
SELECT 
	name,
	birthday,
	IF( current > NOW(), current, next ) AS near,
	today
FROM
	(
SELECT 
    NAME,
	birthday,
	DATE_ADD( nearBirthday, INTERVAL IF ( DAY ( birthday ) = 29 && DAY ( nearBirthday ) = 28, 1, 0 ) DAY ) AS current,
	DATE_ADD( nextBirthday, INTERVAL IF ( DAY ( birthday ) = 29 && DAY ( nextBirthday ) = 28, 1, 0 ) DAY ) AS next,
	today
FROM
	(
SELECT 
    NAME,
	birthday,
	DATE_ADD( birthday, INTERVAL diff YEAR ) AS 'nearBirthday',
	DATE_ADD( birthday, INTERVAL diff + 1 YEAR ) AS 'nextBirthday',
	today 
FROM
	(
SELECT
	CONCAT( last_name, "", first_name ) AS 'name',
	birth_date AS 'birthday',
	( YEAR ( SYSDATE( ) ) - YEAR ( birth_date ) ) AS 'diff',
	SYSDATE( ) AS 'today' 
FROM
	employees 
	) AS temp_a 
	) AS temp_b 
	) AS tmep_c;
SELECT DATE_FORMAT('2090-02-19 23:22:33','%Y-%m-%d')