oracle 从周数获取一周的第一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13706558/
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
Get First Day Of Week From Week Number
提问by user1134307
In Oracle, is there a straightforward way to get the first day of the week given a week number?
在 Oracle 中，是否有一种直接的方法可以获取给定周数的一周中的第一天？
For example, today's date is 12/4/2012. If I run: select to_char(sysdate,'WW') from dual; It returns 49 for the week number.
例如，今天的日期是 12/4/2012。如果我运行： select to_char(sysdate,'WW') from dual; 它返回 49 作为周数。
What I would like to do is somehow return 12/2/2012 for the first day...given week 49 (assuming Sunday as first day of the week).
我想做的是在第一天以某种方式返回 12/2/2012 ......给定第 49 周（假设星期日为一周的第一天）。
Any ideas? Thanks in advance for any help!
有任何想法吗？在此先感谢您的帮助！
回答by DazzaL
try this:
尝试这个：
select next_day(max(d), 'sun') requested_sun
from (select to_date('01-01-2012', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
where to_char(d, 'ww') = 49-1;
just set your year to_date('01-01-2012'and week number-1 49-1as applicable.
只需将您的年份to_date('01-01-2012'和周数设置49-1为适用即可。
the sunday in the 49th week of 2008?
2008 年第 49 周的星期日？
SQL> select next_day(max(d), 'sun') requested_sun
2 from (select to_date('01-01-2008', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
3 where to_char(d, 'ww') = 49-1;
REQUESTED
---------
07-DEC-08
and 2012
和 2012
SQL> select next_day(max(d), 'sun') requested_sun
2 from (select to_date('01-01-2012', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
3 where to_char(d, 'ww') = 49-1;
REQUESTED
---------
02-DEC-12
回答by LuisF
If you have the date, not just the week number, you can try this:
如果你有日期，而不仅仅是周数，你可以试试这个：
- Get the day number of the week of your date with:
to_char(theDate, 'D') - substract that number from your date plus 1, and you'll get the Sunday of that week.
- Add 7 and you'll get the date of end of the week(Saturday).
- 使用以下方法获取您的日期所在周的天数：
to_char(theDate, 'D') - 从您的日期中减去该数字加 1，您将得到该周的星期日。
- 添加 7，您将获得一周结束的日期（星期六）。
Like this:
像这样：
SELECT theDate - to_char(theDate, 'D') + 1 as BeginOfWeek,
theDate,
theDate - to_char(theDate, 'D') + 7 as EndOfWeek
FROM TableName
回答by Chinthaka
Try this,
尝试这个，
select
next_day(trunc(to_date(in_year,'yyyy'),'yyyy') -1,'Mon') + (7 * (in_week - 1))
from dual;
回答by Silahe
I can't comment on questions yet, so I'll add another one. But this is based on @Dazzals answer. His solution doesn't work for week one and for ISO-weeks. Also it doesn't work, if the first day of the week is not sunday, which can be controlled by NLS_SETTINGS.
我还不能评论问题，所以我再补充一个。但这是基于@Dazzals 的回答。他的解决方案不适用于第一周和 ISO 周。如果一周的第一天不是星期日，它也不起作用，这可以由 NLS_SETTINGS 控制。
This one does:
这个是：
SELECT MIN(D)
FROM (SELECT TO_DATE('01-01-2013', 'dd-mm-yyyy') + (ROWNUM-10) D, ROWNUM R
FROM DUAL
CONNECT BY LEVEL <= 376)
WHERE TO_CHAR(D,'IYYYIW') = '201301'
Because we are spanning more than one year, we need to check the year too.
因为我们跨越一年多，所以我们也需要检查一年。
回答by JDunkerley
Using the trunc function @Justin used, I think this is what you want:
使用@Justin 使用的 trunc 函数，我认为这就是您想要的：
select trunc(to_date('2012-01-01', 'YYYY-MM-DD') + (49 - 1) * 7, 'WW') from dual;
回答by elgholm
I ended up doing this:
我最终这样做了：
function getFirstDayOfWeek(y in binary_integer, w in binary_integer) return date
is
td date;
begin
td:=TO_DATE(TO_CHAR(y)||'0101', 'YYYYMMDD');
for c in 0..52
loop
if TO_NUMBER(TO_CHAR(td, 'IW'))=w then
return TRUNC(td, 'IW');
end if;
td:=td+7;
end loop;
return null;
end;
