MySQL日期查询今天、明天、本月、下月、星期、本周第一天、本周最后一天、本周七天日期

雨点打透心脏的1/2处 2024-02-05 19:20 27阅读 0赞

#### 文章目录 ####

*   *   *   *  今天日期
             *  明天日期
             *  本月第一天
             *  本月最后一天
             *  下个月第一天
             *  当前月已过几天
             *  当前月天数
             *  当前月所有日期
             *  获取星期
             *  本周第一天
             *  本周最后一天
             *  获取本周的七天日期

##### 今天日期 #####

select curdate()

![在这里插入图片描述][c73a385dc1374f14bdf1d95cb43adb39.png]

##### 明天日期 #####

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

![在这里插入图片描述][8b3bd295edd74e5793818e6fc55aabe9.png]

##### 本月第一天 #####

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

![在这里插入图片描述][1356ac6375e547c18da4def6adb399d4.png]

##### 本月最后一天 #####

SELECT last_day(curdate()) as date

![在这里插入图片描述][2af00b52f6274294b95044eae2a3076b.png]

##### 下个月第一天 #####

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

![在这里插入图片描述][f628d3b70dbc4ef2818cf7a6533cee1c.png]

##### 当前月已过几天 #####

select day(curdate())

![在这里插入图片描述][090c7f6a7e724ea0a9d5acac92f678dc.png]

##### 当前月天数 #####

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

![在这里插入图片描述][5940a1a37ea1434b84356da776266bd2.png]

##### 当前月所有日期 #####

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

![在这里插入图片描述][8c7a4ee277ff4f02b382183f9a10df1e.png]

##### 获取星期 #####

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

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

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

![在这里插入图片描述][763a5f1a87a84e9985d901c5ec3b79ec.png]

##### 本周第一天 #####

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

![在这里插入图片描述][12c25ea053cc406985c5b5e1c91e866d.png]

##### 本周最后一天 #####

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

![在这里插入图片描述][924d42482dd34cc9bb54bc5828e9b0d4.png]

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

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 ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5 UNION ALL
        SELECT 6
    ) AS numbers;

![在这里插入图片描述][bd4f51a4961b4cb7a0dcfcd1fb6d7cdc.png]

--------------------

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

[c73a385dc1374f14bdf1d95cb43adb39.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/577a177558ab4a7eb080e2f4f544b40c.png
[8b3bd295edd74e5793818e6fc55aabe9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/fb966c44b8a448cf9aa0105c00dca0f2.png
[1356ac6375e547c18da4def6adb399d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/31d7e761aa4f403a9fbf507c6d602b0b.png
[2af00b52f6274294b95044eae2a3076b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/ee3a5311168c444686262ff50ae683e2.png
[f628d3b70dbc4ef2818cf7a6533cee1c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/8eaeb7b266da47e580e8d322d3232e8f.png
[090c7f6a7e724ea0a9d5acac92f678dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c5ea8777722940d7916f94f0fc0bc22f.png
[5940a1a37ea1434b84356da776266bd2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/269cce62e4784780b60171da3744fb3b.png
[8c7a4ee277ff4f02b382183f9a10df1e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/77f8bd93ffef473b8d855cec96dde5c0.png
[763a5f1a87a84e9985d901c5ec3b79ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/d1830291b1324745b9d498d4111b1762.png
[12c25ea053cc406985c5b5e1c91e866d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/eea1049a62b440b596fa24db9106d2ce.png
[924d42482dd34cc9bb54bc5828e9b0d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/7c3e993d3c524324baeba8ae1491dfdf.png
[bd4f51a4961b4cb7a0dcfcd1fb6d7cdc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c1b6822606ed47469c589ec8bc3a84d5.png