mysql中，输入一个日期，动态的获取每周和每月的实时最后一天

mysql中，输入一个日期，动态的获取每周和每月的实时最后一天

SELECT
	v.dayStr AS '选定的日期',
	-- 系统当前日期
	(
		SELECT
			DATE_FORMAT(NOW(), '%Y-%m-%d') AS nowDate
		FROM
			DUAL
	) AS '系统当前日期',
	-- 指定日期对应的周日日期
	(
		SELECT
			date_sub(
				v.dayStr,
				INTERVAL WEEKDAY(v.dayStr) - 6 DAY
			) AS lastDate
		FROM
			DUAL
	) AS '指定日期对应的周日日期',
	-- 本周实时最后一天
	(
		SELECT

		IF (
			TIMESTAMPDIFF(
				DAY,
				DATE_FORMAT(NOW(), '%Y-%m-%d'),
				(
					SELECT
						date_sub(
							v.dayStr,
							INTERVAL WEEKDAY(v.dayStr) - 6 DAY
						)
				)
			) > 0,
			DATE_FORMAT(NOW(), '%Y-%m-%d'),
			(
				SELECT
					date_sub(
						v.dayStr,
						INTERVAL WEEKDAY(v.dayStr) - 6 DAY
					)
			)
		) AS actualLastDate
		FROM
			DUAL
	) AS '本周实时最后一天',
	-- 本周已过完或正在过的天数
	(
		SELECT

		IF (
			DAYOFWEEK(

				IF (
					TIMESTAMPDIFF(
						DAY,
						DATE_FORMAT(NOW(), '%Y-%m-%d'),
						(
							SELECT
								date_sub(
									v.dayStr,
									INTERVAL WEEKDAY(v.dayStr) - 6 DAY
								)
						)
					) > 0,
					DATE_FORMAT(NOW(), '%Y-%m-%d'),
					(
						SELECT
							date_sub(
								v.dayStr,
								INTERVAL WEEKDAY(v.dayStr) - 6 DAY
							)
					)
				)
			) = 1,
			7,
			DAYOFWEEK(

				IF (
					TIMESTAMPDIFF(
						DAY,
						DATE_FORMAT(NOW(), '%Y-%m-%d'),
						(
							SELECT
								date_sub(
									v.dayStr,
									INTERVAL WEEKDAY(v.dayStr) - 6 DAY
								)
						)
					) > 0,
					DATE_FORMAT(NOW(), '%Y-%m-%d'),
					(
						SELECT
							date_sub(
								v.dayStr,
								INTERVAL WEEKDAY(v.dayStr) - 6 DAY
							)
					)
				)
			) - 1
		) AS actualLastDays
		FROM
			DUAL
	) AS '本周已过完或正在过的天数',
	-- 本月第一天
	DATE_ADD(v.dayStr,interval -day(v.dayStr)+1 day) as '本月第一天',
	-- 本月最后一天
	last_day(v.dayStr) as '本月最后一天',
	-- 本月已过完或正在过的最后一天
	(
		select IF (
			TIMESTAMPDIFF(
				DAY,
				DATE_FORMAT(NOW(), '%Y-%m-%d'),
				last_day(v.dayStr)
			) > 0,
			DATE_FORMAT(NOW(), '%Y-%m-%d'),
			last_day(v.dayStr)
		) AS actualMonthLastDate from DUAL
	) as '本月已过完或正在过的最后一天',
	-- 本月实际已过或正在过的天数
	(
		if(
			(IF (
				TIMESTAMPDIFF(
					DAY,
					DATE_FORMAT(NOW(), '%Y-%m-%d'),
					last_day(v.dayStr)
				) > 0,
				0,
				1
			))=0,
			DATE_FORMAT((
				select IF (
					TIMESTAMPDIFF(
						DAY,
						DATE_FORMAT(NOW(), '%Y-%m-%d'),
						last_day(v.dayStr)
					) > 0,
					DATE_FORMAT(NOW(), '%Y-%m-%d'),
					last_day(v.dayStr)
				) AS actualMonthLastDate from DUAL
			),'%d'),
			DATE_FORMAT((
				select last_day(v.dayStr) from dual
			),'%d')
		)
	) as '本月实际已过或正在过的天数'
FROM
	(
			select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) dayStr from
			 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
			 7 union select 8 union select 9) t0,
			 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
			 7 union select 8 union select 9) t1,
			 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
			 7 union select 8 union select 9) t2,
			 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
			 7 union select 8 union select 9) t3,
			 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
			 7 union select 8 union select 9) t4
	)v
WHERE
	v.dayStr >= '2022-08-01'
AND v.dayStr <= '2022-09-16'

效果截图如下：