Oracle查询获取特定日期 [英] Oracle query for getting particular Date
问题描述
我有一个oracle查询，该查询返回下一个mont的第一个星期日. 现在我有一个条件在这里检查是否已经过的日期是本月的第一个星期日，那么我们需要本月的第二个星期日. 否则，下个月的第一个星期日.
I have an oracle query which returns next mont first sunday. now i have a condition here to check if the date which has passed is first sunday of current month, then we need second sunday in current month. Else, next month first sunday.
我的查询:
DEF startDate = to_date('somedate', 'dd/mm/yyyy');
Select next_day(last_day(&startDate),'Sunday' ) from dual ;
预期输出: 如果我们输入2018年7月1日，则必须返回2018年7月8日(第二个星期日)，除第一个星期天(如2018年7月2日)以外的其他任何一天，都必须返回2018年8月5日.
Expected output: if we input 1st july 2018, it has to return 8th july 2018(second sunday) else, any other day apart from first sunday like, (2nd july 2018), it has to return 5th Aug 2018.
Input Expected Output
01-Jul-18 08-Jul-18,
02-Jul-18 05-Aug-18,
05-Aug-18 12-Aug-18,
19-Aug-18 02-Sep-18.
推荐答案
根据您问题中的描述和评论，您需要以下内容:
Based on the description in your question and comments you want something like:
case when start_date = next_day(trunc(start_date, 'MM') - 1, 'Sunday') -- date is on first sunday
then next_day(start_date, 'Sunday') -- next Sunday, which is second in month
else next_day(last_day(start_date), 'Sunday') -- first Sunday of next month
end
在CTE中有一些示例日期，包括一些讨论过的日期，还有其他一些:
With some sample dates in a CTE, including some discussed but also others:
with cte (start_date) as (
select date '2018-05-30' from dual
union all select date '2018-06-01' from dual
union all select date '2018-06-02' from dual
union all select date '2018-06-03' from dual
union all select date '2018-06-04' from dual
union all select date '2018-06-30' from dual
union all select date '2018-07-01' from dual
union all select date '2018-07-02' from dual
union all select date '2018-07-03' from dual
union all select date '2018-07-04' from dual
union all select date '2018-07-05' from dual
union all select date '2018-07-06' from dual
union all select date '2018-07-07' from dual
union all select date '2018-07-08' from dual
union all select date '2018-07-31' from dual
union all select date '2018-08-01' from dual
union all select date '2018-08-02' from dual
union all select date '2018-08-03' from dual
union all select date '2018-08-04' from dual
union all select date '2018-08-05' from dual
union all select date '2018-08-06' from dual
)
select start_date,
to_char(start_date, 'Dy') as day,
case when to_char(start_date, 'Dy') = 'Sun'
then 'Yes' else 'No' end as is_sunday,
case when start_date = next_day(trunc(start_date, 'MM') - 1, 'Sunday')
then 'Yes' else 'No' end as is_first_sunday,
next_day(trunc(start_date, 'MM') - 1, 'Sunday') as first_sun_this_month,
next_day(trunc(start_date, 'MM') + 6, 'Sunday') as second_sun_this_month,
next_day(last_day(start_date), 'Sunday') as first_sun_next_month,
case when start_date = next_day(trunc(start_date, 'MM') - 1, 'Sunday') -- date is on first sunday
then next_day(start_date, 'Sunday') -- next Sunday, which is second in month
else next_day(last_day(start_date), 'Sunday') -- first Sunday of next month
end as result
from cte;
获取
START_DATE DAY IS_ IS_ FIRST_SUN_ SECOND_SUN FIRST_SUN_ RESULT
---------- ------------ --- --- ---------- ---------- ---------- ----------
2018-05-30 Wed No No 2018-05-06 2018-05-13 2018-06-03 2018-06-03
2018-06-01 Fri No No 2018-06-03 2018-06-10 2018-07-01 2018-07-01
2018-06-02 Sat No No 2018-06-03 2018-06-10 2018-07-01 2018-07-01
2018-06-03 Sun Yes Yes 2018-06-03 2018-06-10 2018-07-01 2018-06-10
2018-06-04 Mon No No 2018-06-03 2018-06-10 2018-07-01 2018-07-01
2018-06-30 Sat No No 2018-06-03 2018-06-10 2018-07-01 2018-07-01
2018-07-01 Sun Yes Yes 2018-07-01 2018-07-08 2018-08-05 2018-07-08
2018-07-02 Mon No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-03 Tue No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-04 Wed No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-05 Thu No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-06 Fri No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-07 Sat No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-08 Sun Yes No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-31 Tue No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-08-01 Wed No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
2018-08-02 Thu No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
2018-08-03 Fri No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
2018-08-04 Sat No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
2018-08-05 Sun Yes Yes 2018-08-05 2018-08-12 2018-09-02 2018-08-12
2018-08-06 Mon No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
result列是您感兴趣的列，其他列只是尝试显示其工作原理.
The result column is the one you're interested in, the others just try to show the working a bit.
与输入日期相比，结果日期不按顺序排列看起来很奇怪-2018-06-03进入2018--06-10，然后之前和之后的那些进入2018-07-01.但这似乎就是您想要的.
It looks odd to have the result dates out of sequence compared to the input dates - 2018-06-03 going to 2018--06-10 then those both before and after it go to 2018-07-01. But that seems to be what you want.
这篇关于Oracle查询获取特定日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
