使用 SQL 生成日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/418318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate a range of dates using SQL
提问by George Mauer
I have a SQL query that takes a date parameter (if I were to throw it into a function) and I need to run it on every day of the last year.
我有一个 SQL 查询，它接受一个日期参数（如果我要将它放入一个函数中），我需要在去年的每一天运行它。
How to generate a list of the last 365 days, so I can use straight-up SQL to do this?
如何生成过去 365 天的列表，以便我可以使用直接 SQL 来执行此操作？
Obviously generating a list 0..364 would work, too, since I could always:
显然生成一个列表 0..364 也可以，因为我总是可以：
SELECT SYSDATE - val FROM (...);
回答by user34850
There's no need to use extra large tables or ALL_OBJECTS table:
无需使用超大表或 ALL_OBJECTS 表：
SELECT TRUNC (SYSDATE - ROWNUM) dt
FROM DUAL CONNECT BY ROWNUM < 366
will do the trick.
会做的伎俩。
回答by user853322
SELECT (sysdate-365 + (LEVEL -1)) AS DATES
FROM DUAL connect by level <=( sysdate-(sysdate-365))
if a 'from' and a 'to' date is replaced in place of sysdate and sysdate-365, the output will be a range of dates between the from and to date.
如果 'from' 和 'to' 日期被替换为 sysdate 和 sysdate-365，则输出将是起始日期和截止日期之间的日期范围。
回答by AndrewMcCoist
Recently I had a similar problem and solved it with this easy query:
最近我遇到了类似的问题，并通过这个简单的查询解决了它：
SELECT
(to_date(:p_to_date,'DD-MM-YYYY') - level + 1) AS day
FROM
dual
CONNECT BY LEVEL <= (to_date(:p_to_date,'DD-MM-YYYY') - to_date(:p_from_date,'DD-MM-YYYY') + 1);
Example
例子
SELECT
(to_date('01-05-2015','DD-MM-YYYY') - level + 1) AS day
FROM
dual
CONNECT BY LEVEL <= (to_date('01-05-2015','DD-MM-YYYY') - to_date('01-04-2015','DD-MM-YYYY') + 1);
Result
结果
01-05-2015 00:00:00
30-04-2015 00:00:00
29-04-2015 00:00:00
28-04-2015 00:00:00
27-04-2015 00:00:00
26-04-2015 00:00:00
25-04-2015 00:00:00
24-04-2015 00:00:00
23-04-2015 00:00:00
22-04-2015 00:00:00
21-04-2015 00:00:00
20-04-2015 00:00:00
19-04-2015 00:00:00
18-04-2015 00:00:00
17-04-2015 00:00:00
16-04-2015 00:00:00
15-04-2015 00:00:00
14-04-2015 00:00:00
13-04-2015 00:00:00
12-04-2015 00:00:00
11-04-2015 00:00:00
10-04-2015 00:00:00
09-04-2015 00:00:00
08-04-2015 00:00:00
07-04-2015 00:00:00
06-04-2015 00:00:00
05-04-2015 00:00:00
04-04-2015 00:00:00
03-04-2015 00:00:00
02-04-2015 00:00:00
01-04-2015 00:00:00
回答by Igor Krupitsky
Date range between 12/31/1996 and 12/31/2020
日期范围介于 12/31/1996 和 12/31/2020 之间
SELECT dt, to_char(dt, 'MM/DD/YYYY') as date_name,
EXTRACT(year from dt) as year,
EXTRACT(year from fiscal_dt) as fiscal_year,
initcap(to_char(dt, 'MON')) as month,
to_char(dt, 'YYYY') || ' ' || initcap(to_char(dt, 'MON')) as year_month,
to_char(fiscal_dt, 'YYYY') || ' ' || initcap(to_char(dt, 'MON')) as fiscal_year_month,
EXTRACT(year from dt)*100 + EXTRACT(month from dt) as year_month_id,
EXTRACT(year from fiscal_dt)*100 + EXTRACT(month from fiscal_dt) as fiscal_year_month_id,
to_char(dt, 'YYYY') || ' Q' || to_char(dt, 'Q') as quarter,
to_char(fiscal_dt, 'YYYY') || ' Q' || to_char(fiscal_dt, 'Q') as fiscal_quarter
--, EXTRACT(day from dt) as day_of_month, to_char(dt, 'YYYY-WW') as week_of_year, to_char(dt, 'D') as day_of_week
FROM (
SELECT dt, add_months(dt, 6) as fiscal_dt --starts July 1st
FROM (
SELECT TO_DATE('12/31/1996', 'mm/dd/yyyy') + ROWNUM as dt
FROM DUAL CONNECT BY ROWNUM < 366 * 30 --30 years
)
WHERE dt <= TO_DATE('12/31/2020', 'mm/dd/yyyy')
)
回答by Chris
About a year and a half too late, but for posterity here is a version for Teradata:
大约一年半太晚了，但为了后代，这里是 Teradata 的一个版本：
SELECT calendar_date
FROM SYS_CALENDAR.Calendar
WHERE SYS_CALENDAR.Calendar.calendar_date between '2010-01-01' (date) and '2010-01-03' (date)
回答by Gary Myers
Oracle specific, and doesn't rely on pre-existing large tables or complicated system views over data dictionary objects.
Oracle 特定的，并且不依赖于预先存在的大表或数据字典对象上的复杂系统视图。
SELECT c1 from dual
MODEL DIMENSION BY (1 as rn) MEASURES (sysdate as c1)
RULES ITERATE (365)
(c1[ITERATION_NUMBER]=SYSDATE-ITERATION_NUMBER)
order by 1
回答by David Aldridge
A method quite frequently used in Oracle is something like this:
Oracle 中经常使用的一种方法是这样的：
select trunc(sysdate)-rn
from
( select rownum rn
from dual
connect by level <= 365)
/
Personally, if an application has a need for a list of dates then I'd just create a table with them, or create a table with a series of integers up to something ridiculous like one million that can be used for this sort of thing.
就我个人而言，如果应用程序需要日期列表，那么我只需要用它们创建一个表，或者创建一个包含一系列整数的表，最多可达一百万，可用于此类事情。
回答by George Mauer
Ahahaha, here's a funny way I just came up with to do this:
啊哈哈哈哈，这是我想出的一个有趣的方法来做到这一点：
select SYSDATE - ROWNUM
from shipment_weights sw
where ROWNUM < 365;
where shipment_weights is any large table;
其中shipment_weights 是任何大表；
回答by Victor H
A week from 6 months back
6 个月前的一周
SELECT (date'2015-08-03' + (LEVEL-1)) AS DATES
FROM DUAL
where ROWNUM < 8
connect by level <= (sysdate-date'2015-08-03');
if you omit ROWNUM you get 50 rows only, independent of the value.
如果省略 ROWNUM，则仅获得 50 行，与值无关。
回答by Suddha
I had the same requirement - I just use this. User enters the number of days by which he/she wants to limit the calendar range to.
我有同样的要求 - 我只是使用这个。用户输入他/她希望将日历范围限制到的天数。
SELECT DAY, offset
FROM (SELECT to_char(SYSDATE, 'DD-MON-YYYY') AS DAY, 0 AS offset
FROM DUAL
UNION ALL
SELECT to_char(SYSDATE - rownum, 'DD-MON-YYYY'), rownum
FROM all_objects d)
where offset <= &No_of_days
I use the above result set as driving view in LEFT OUTER JOINwith other views involving tables which have dates.
我使用上述结果集作为驱动视图，LEFT OUTER JOIN以及其他涉及具有日期的表的视图。
