今天日期

select curdate()

明天日期

select DATE_SUB(curdate(),INTERVAL -1 DAY) AS tomorrow

本月第一天

select date_add(curdate(),interval-day(curdate())+1 day) as date

本月最后一天

SELECT last_day(curdate()) as date

下个月第一天

select date_add(curdate() - day(curdate()) +1,interval 1 month) AS nextMfday

当前月已过几天

select day(curdate())

当前月天数

select day(date_add(date_add(curdate(),interval 1 month),interval -day(curdate()) day))  AS curMdays

当前月所有日期

select date from (
SELECT DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as date
FROM ( SELECT @xi:=@xi+1 as xc from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,  (SELECT @xi:=0) xc0 
) xcxc) x0 where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day))
ORDER BY date

获取星期

使用 WEEKDAY() 函数获取星期。

SELECT WEEKDAY('2023-12-26') AS '星期';

这里返回一个整数，其中0表示星期一，1表示星期二，以此类推，6表示星期日

本周第一天

SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS '本周第一天';

本周最后一天

SELECT DATE_ADD(CURDATE(), INTERVAL 6 - WEEKDAY(CURDATE()) DAY) AS '本周最后一天';

示例：对某个表筛选本周日期

SELECT * FROM tb_name 
where create_date BETWEEN DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AND DATE_ADD(CURDATE(), INTERVAL 6 - WEEKDAY(CURDATE()) DAY);

获取本周的七天日期

SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL n DAY) AS date,
case when n=0 then '星期一'when n=1 then '星期二'when n=2 then '星期三'when n=3 then '星期四'when n=4 then '星期五'when n=5 then '星期六'when n=6 then '星期日'else ''end as weekStr
FROM (SELECT 0 AS n UNION ALLSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 UNION ALLSELECT 6
) AS numbers;

---

更多技术干货，请持续关注程序员大佬超。
原创不易，转载请注明出处。