SQL 如何在 Oracle 中获取一周的第一天和最后一天？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5515514/
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 to get first and last day of week in Oracle?
提问by Alex
I need to get the first day and the last day of the week from some strings that have the format like this:
我需要从一些格式如下的字符串中获取一周的第一天和最后一天：
'201118'
'201118'
where 2011 is the year and 18 is the number of the week. Knowing the number of the week, how do I get the first and the last day of the week?
其中 2011 是年份，18 是周数。知道星期几，我如何获得一周的第一天和最后一天？
How do I do this?
我该怎么做呢？
回答by user3378814
WEEK
星期
select TRUNC(sysdate, 'iw') AS iso_week_start_date,
TRUNC(sysdate, 'iw') + 7 - 1/86400 AS iso_week_end_date
from dual;
MONTH
月
select
TRUNC (sysdate, 'mm') AS month_start_date,
LAST_DAY (TRUNC (sysdate, 'mm')) + 1 - 1/86400 AS month_end_date
from dual;
回答by user2867308
If you are using Oracle, this code can help you:
如果您使用的是 Oracle，此代码可以帮助您：
select
TRUNC(sysdate, 'YEAR') Start_of_the_year,
TRUNC(sysdate, 'MONTH') Start_of_the_month,
TRUNC(sysdate, 'DAY') start_of_the_week,
TRUNC(sysdate+365, 'YEAR')-1 End_of_the_year,
TRUNC(sysdate+30, 'MONTH')-1 End_of_the_month,
TRUNC(sysdate+6, 'DAY')-1 end_of_the_week
from dual;
select
TRUNC(sysdate, 'YEAR') Start_of_the_year,
TRUNC(sysdate+365, 'YEAR')-1 End_of_the_year,
TRUNC(sysdate, 'MONTH') Start_of_the_month,
TRUNC(sysdate+30, 'MONTH')-1 End_of_the_month,
TRUNC(sysdate, 'DAY')+1 start_of_the_week, -- starting Monday
TRUNC(sysdate+6, 'DAY') end_of_the_week -- finish Sunday
from dual;
回答by Rob van Wijk
SQL> var P_YEARWEEK varchar2(6)
SQL> exec :P_YEARWEEK := '201118'
PL/SQL procedure successfully completed.
SQL> with t as
2 ( select substr(:P_YEARWEEK,1,4) year
3 , substr(:P_YEARWEEK,5,2) week
4 from dual
5 )
6 select year
7 , week
8 , trunc(to_date(year,'yyyy'),'yyyy') january_1
9 , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') monday_week_1
10 , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') + (week - 1) * 7 start_of_the_week
11 , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') + week * 7 - 1 end_of_the_week
12 from t
13 /
YEAR WE JANUARY_1 MONDAY_WEEK_1 START_OF_THE_WEEK END_OF_THE_WEEK
---- -- ------------------- ------------------- ------------------- -------------------
2011 18 01-01-2011 00:00:00 27-12-2010 00:00:00 25-04-2011 00:00:00 01-05-2011 00:00:00
1 row selected.
Regards,
Rob.
问候，
罗布。
回答by shibi
SELECT NEXT_DAY (TO_DATE ('01/01/'||SUBSTR('201118',1,4),'MM/DD/YYYY')+(TO_NUMBER(SUBSTR('201118',5,2))*7)-3,'SUNDAY')-7 first_day_wk,
NEXT_DAY (TO_DATE ('01/01/'||SUBSTR('201118',1,4),'MM/DD/YYYY')+(TO_NUMBER(SUBSTR('201118',5,2))*7)-3,'SATURDAY') last_day_wk FROM dual
回答by Dave Costa
Assuming you mean weeks relative to the first day of the year ...
假设您的意思是相对于一年中的第一天的几周......
SELECT first_day_of_week, first_day_of_week+6 last_day_of_week
FROM (
SELECT TO_DATE(YEAR||'0101','YYYYMMDD') + 7 * (week-1) first_day_of_week
FROM (
SELECT substr(yearweek,1,4) YEAR, to_number(substr(yearweek,5)) week
FROM (
SELECT '201118' yearweek FROM dual
)
)
)
;
回答by Ronnis
Unless this is a one-off data conversion, chances are you will benefit from using a calendar table.
除非这是一次性的数据转换，否则您很有可能会从使用日历表中受益。
Having such a table makes it really easy to filter or aggregate data for non-standard periods in additionto regular ISO weeks. Weeks usually behave a bit differently across companies and the departments within them. As soon as you leave "ISO-land" the built-in date functions can't help you.
有了这样的表格，除了常规的 ISO 周之外，过滤或聚合非标准时期的数据变得非常容易。周通常在公司及其内部部门之间的表现略有不同。一旦你离开“ISO-land”，内置的日期功能就帮不上忙了。
create table calender(
day date not null -- Truncated date
,iso_year_week number(6) not null -- ISO Year week (IYYYIW)
,retail_week number(6) not null -- Monday to Sunday (YYYYWW)
,purchase_week number(6) not null -- Sunday to Saturday (YYYYWW)
,primary key(day)
);
You can either create additional tables for "purchase_weeks" or "retail_weeks", or simply aggregate on the fly:
您可以为“purchase_weeks”或“retail_weeks”创建额外的表，或者简单地动态聚合：
select a.colA
,a.colB
,b.first_day
,b.last_day
from your_table_with_weeks a
join (select iso_year_week
,min(day) as first_day
,max(day) as last_day
from calendar
group
by iso_year_week
) b on(a.iso_year_week = b.iso_year_week)
If you process a large number of records, aggregating on the fly won't make a noticable difference, but if you are performing single-row you would benefit from creating tables for the weeks as well.
如果您处理大量记录，动态聚合不会产生显着差异，但如果您执行单行，您也将从创建数周的表中受益。
Using calendar tables provides a subtle performance benefit in that the optimizer can provide better estimates on static columns than on nested add_months(to_date(to_char()))function calls.
使用日历表提供了微妙的性能优势，因为优化器可以对静态列提供比嵌套add_months(to_date(to_char()))函数调用更好的估计。
回答by Andriy M
You could try this approach:
你可以试试这个方法：
Get the current date.
Add the the number of years which is equal to the difference between the current date's year and the specified year.
Subtract the number of days that is the last obtained date's week day's number (and it will give us the last day of some week).
Add the number of weeks which is the difference between the last obtained date's week number and the specified week number, and that will yield the last day of the desired week.
Subtract 6 days to obtain the first day.
获取当前日期。
添加等于当前日期年份与指定年份之间的差的年数。
减去最后获得日期的星期数的天数（它会给我们某个星期的最后一天）。
添加上次获取日期的周数与指定周数之间的差的周数，这将产生所需周的最后一天。
减去6天得到第一天。
回答by Alex
Actually, I did something like this:
实际上，我做了这样的事情：
select case myconfigtable.valpar
when 'WEEK' then to_char(next_day(datetime-7,'Monday'),'DD/MM/YYYY')|| ' - '|| to_char(next_day(datetime,'Sunday'),'DD/MM/YYYY')
when 'MONTH' then to_char(to_date(yearweek,'yyyyMM'),'DD/MM/YYYY') || ' - '|| to_char(last_day(to_date(yearweek,'yyyyMM')),'DD/MM/YYYY')
else 'NA'
end
from
(
select to_date(YEAR||'01','YYYYMM') + 7 * (WEEK - 1) datetime, yearweek
from
(
select substr(yearweek,1,4) YEAR,
to_number(substr(yearweek,5)) WEEK,
yearweek
from (select '201018' yearweek from dual
)
)
), myconfigtable myconfigtable
where myconfigtable.codpar='TYPEOFPERIOD'
回答by cem
Yet another solution (Monday is the first day):
另一个解决方案（星期一是第一天）：
select
to_char(sysdate - to_char(sysdate, 'd') + 2, 'yyyymmdd') first_day_of_week
, to_char(sysdate - to_char(sysdate, 'd') + 8, 'yyyymmdd') last_day_of_week
from
dual
回答by Ivan Fretes
Another solution is
另一个解决方案是
SELECT
ROUND((TRUNC(SYSDATE) - TRUNC(SYSDATE, 'YEAR')) / 7,0) CANTWEEK,
NEXT_DAY(SYSDATE, 'SUNDAY') - 7 FIRSTDAY,
NEXT_DAY(SYSDATE, 'SUNDAY') - 1 LASTDAY
FROM DUAL
You must concat the cantweek with the year
您必须将 cantweek 与年份连接起来
