文章目录
- 今天日期
- 明天日期
- 本月第一天
- 本月最后一天
- 下个月第一天
- 当前月已过几天
- 当前月天数
- 当前月所有日期
- 获取星期
- 本周第一天
- 本周最后一天
- 获取本周的七天日期
今天日期
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;
更多技术干货,请持续关注程序员大佬超。
原创不易,转载请注明出处。