如何使用 Oracle SQL 从 sysdate 的月份中获取所有日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31462241/
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
How do I get all dates from sysdate's month with Oracle SQL?
提问by Midoes
I try to get a query without parameters to obtain the list of dates from the current month.
我尝试获取不带参数的查询以获取当月的日期列表。
Something like this:
像这样的东西：
SYSDATE = 16/07/15
系统日期 = 16/07/15
I want the next list:
我想要下一个列表：
01/07/15
02/07/15
...
30/07/15
31/07/15
01/07/15
02/07/15
...
30/07/15
31/07/15
采纳答案by Mr. Llama
Here's what I got to work:
这是我的工作：
SELECT TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS day
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'MM') = TRUNC(SYSDATE, 'MM')
;
The key in this query is TRUNC(SYSDATE, 'MONTH')which is the first day of the current month.
We use hierarchical queries to keep adding one day to the first day of the month until the value is no longer in the current month.
We use LEVEL - 1because LEVELstarts from 1 and we need it to start from zero.
这个查询的关键是TRUNC(SYSDATE, 'MONTH')哪个是当月的第一天。
我们使用分层查询不断向该月的第一天添加一天，直到该值不再是当月。
我们使用LEVEL - 1因为LEVEL从 1 开始，我们需要它从零开始。
Here's a pseudo-query for what the above query does:
这是上述查询功能的伪查询：
SELECT (start_of_month + days) AS day
FROM dual
WHILE MONTH_OF(start_of_month + days) = current_month
This query be a bit easier to understand:
这个查询更容易理解：
SELECT *
FROM
(
SELECT TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS day
FROM dual
CONNECT BY LEVEL <= 32
)
WHERE EXTRACT(MONTH FROM day) = EXTRACT(MONTH FROM SYSDATE)
回答by Coding Enthusiast
Selects all the days for current month
选择当月的所有天数
SELECT TO_CHAR (TRUNC (SYSDATE, 'MM'), 'YYYYMMDD')+(LEVEL - 1) each_date
FROM DUAL a
CONNECT BY LEVEL < (TO_NUMBER (TO_CHAR (TRUNC (SYSDATE, 'MM') - 1, 'DD'))+1)
回答by Nick
This is a trick using connect by. I truncate the date at the month level, effectively setting it to the 1st day of the month, add a day for each "level" and then filter for any day that occurs outside the month.
这是一个使用 connect by 的技巧。我在月级别截断日期，有效地将其设置为该月的第一天，为每个“级别”添加一天，然后过滤该月之外发生的任何一天。
select day_of_month
from
(select (level - 1) + trunc(to_date('07/16/2015','MM/DD/YYYY'),'MM') day_of_month
from
dual
connect by level <= 31)
where day_of_month < add_months(trunc(to_date('07/16/2015','MM/DD/YYYY'),'MM'),1);
回答by GriffeyDog
This should work:
这应该有效：
select trunc(sysdate, 'MONTH') + rownum - 1
from dual
connect by rownum <= to_number(to_char(last_day(sysdate), 'DD'));
