sql - Oracle递归查询获取日期范围

Question

我需要使用以下查询来获取日期范围

with DATETABLE (tempdate) AS
( 
SELECT trunc(to_date('2020-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss')) AS tempdate from dual
UNION ALL 
SELECT tempdate + 1 FROM DATETABLE 
where tempdate < to_date('2020-02-01 01:00:00','yyyy-mm-dd hh24:mi:ss')
 )

select tempdate from DATETABLE

我有错误

ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 -  "expression must have same datatype as corresponding expression"
*Cause:    
*Action:
Error at Line: 5 Column: 20

如何解决？

标签： sqloracledaterange

Answer 1

这将适用于所有版本11g及更高版本：

with t AS
( 
  SELECT DATE '2020-01-01' + level -1 AS tempdate from dual
  CONNECT BY LEVEL <= DATE '2020-02-01' - DATE '2020-01-01'
 )
SELECT tempdate FROM t;

输出：

TEMPDATE
--------
01-01-20
02-01-20
03-01-20
04-01-20
05-01-20
06-01-20
07-01-20
08-01-20
09-01-20
10-01-20
11-01-20
12-01-20
13-01-20
14-01-20
15-01-20
16-01-20
17-01-20
18-01-20
19-01-20
20-01-20
21-01-20
22-01-20
23-01-20
24-01-20
25-01-20
26-01-20
27-01-20
28-01-20
29-01-20
30-01-20
31-01-20

31 rows selected.