欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mysql与Oracle日期sql

程序员文章站 2024-02-14 09:49:58
...

1.Mysql查询时间段间的每一天

select a.Date 
from (
    select '2020-08-30' - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2020-08-07' and '2020-08-30' 

Mysql与Oracle日期sql

2.oracle 查询时间段的每一天

SELECT
	TO_CHAR( TO_DATE( '2020-05-03', 'YYYY-MM-DD' ) + ROWNUM - 1, 'YYYY-MM-DD' ) dateStat 
FROM
	DUAL CONNECT BY ROWNUM <= TO_DATE( '2020-06-03', 'YYYY-MM-DD' ) - TO_DATE( '2020-05-03', 'YYYY-MM-DD' ) + 1

3.Mysql 查询某个月的中的自然周

(
	SELECT DATE_FORMAT(ADDDATE(y.first, x.d - 1),'%x年-第%v周') as `week`
	FROM
	(SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
	SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
	SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
	SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
	SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x,
	(SELECT CONCAT('2020-08','-01') as FIRST, DAY(LAST_DAY(str_to_date('2020-08','%Y-%m'))) AS last) y
	WHERE x.d <= y.last  group by `week`)

Mysql与Oracle日期sql