MySQL----获取当前日期当月第一天，最后一天

获取当前日期select curdate();结果： 2021-10-15获取当前日期为几号day(curdate())结果： 15在当前日期上加上时间间隔DATE_ADD(curdate(),interval 2 day)结果： 2021-10-17获取本月第一天select DATE_ADD(curdate(),interval -day(curdate())+1 day)-- 获取本月第一天

张邵

20191人浏览 · 2021-10-15 16:20:02

张邵 · 2021-10-15 16:20:02 发布

获取当前日期
select curdate();
结果： 2021-10-15

获取当前日期为几号
day(curdate())
结果： 15

在当前日期上加上时间间隔
DATE_ADD(curdate(),interval 2 day)
结果： 2021-10-17

获取本月第一天

select DATE_ADD(curdate(),interval -day(curdate())+1 day)   -- 获取本月第一天

获取当月最后一天

select last_day(curdate());   -- 获取当月最后一天

获取下月第一天

select date_add(curdate()-day(curdate())+1,interval 1 month ) -- 获取下个月的第一天

获取当前月的天数

select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual -- 获取当前月的天数

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

注意：如果日期格式是： 2020-10 需要使用concat() 拼接一个01然后进行计算
如：

select DATE_ADD(concat("2020-10","-01"),interval 1-day(concat("2020-10","-01")) day)   -- 获取本月第一天

当前日期

select curdate()

明天日期

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

当前月已过天数

select day(curdate())

最近7天日期

SELECT @s :=@s + 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := -7) temp
WHERE @s < 0
ORDER BY 'date'

本周日期

SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < 6 - WEEKDAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := WEEKDAY(CURRENT_DATE) + 1) temp
WHERE @s > 0
ORDER BY `date`

当月所有日期

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`

往后一个月日期

SET @days = TIMESTAMPDIFF( DAY, ADDDATE( date( now( ) ),- 31 ), now( ) );
SET @d = - 1;
SELECT
	@d := @d + 1 AS 'index',
	ADDDATE( ADDDATE( date( now( ) ),- 0), @d ) AS date
FROM
	(
	SELECT
		a 
	FROM
		( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS a
		JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 
		LIMIT 31 
	) d 
WHERE
	@d < @days