1,获取当前日期的年初第一天和年末的最后一天
select CONCAT(YEAR(CURDATE()),'-01-01')
select CONCAT(YEAR(CURDATE()),'-12-31')
2,获取当前日期的上一年
select YEAR(CURDATE())-1
3,从日期(‘2022-01-11’)中提取年份
SELECT DATE_FORMAT('2022-01-11', '%Y') AS year
4,获取报告期是当前日期的近三年的数据
SELECT * FROM your_table WHERE rpt_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 YEAR) AND CURDATE();
5,将日期减去一天
SELECT DATE_SUB('2022-09-09', INTERVAL 1 DAY) AS new_date
6,从日期中(‘2022-09-08’)提取年月’2022-09’
SELECT DATE_FORMAT('2022-09-08', '%Y-%m') AS formatted_date;
7,获取当前日期的月份
SELECT MONTH(CURDATE()) AS month;
8,计算两个日期的差,显示成年
SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2023-09-01') AS DifferenceInYears;
9,计算两个日期的差,显示成年月
SELECT
CONCAT(TIMESTAMPDIFF(YEAR, '2020-01-01', '2023-09-11'), ' 年 ',
TIMESTAMPDIFF(MONTH, '2020-01-01', '2023-09-11') % 12, ' 月') AS duration
FROM
dual;
10,计算两个日期的差,显示成年和天数
SELECT
CONCAT(TIMESTAMPDIFF(YEAR, '2020-01-01', '2023-09-11'), ' 年 ',
TIMESTAMPDIFF(DAY, '2020-01-01', '2023-09-11') % 365,'天') AS days
FROM dual;