oracle 使用oracle日期函数获取周数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38692854/
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 week number using oracle date function?
提问by user6662042
I used this query to get week number but getting wrong output of first week but actually its a second week of august-2016.
我使用此查询获取周数，但第一周的输出错误，但实际上是 2016 年 8 月的第二周。
select
to_char(TO_DATE('07-08-2016','dd-mm- rrrr'), 'w') week ,
to_char(TO_DATE('07-08-2016','dd-mm- rrrr'), 'd') day-no,
to_char(TO_DATE('07-08-2016','dd-mm- rrrr'), 'Day') Day
from dual
回答by Mottor
Read carefully:
仔细读：
W - Week of month (1-5) where week 1 starts on the first dayof the month and ends on the seventh.
W - 月份中的第 (1-5) 周，其中第 1 周从该月的第一天开始，并在第七天结束。
WITH t AS (SELECT TO_DATE ('07-07-2016', 'dd-mm-rrrr') d FROM DUAL)
SELECT TO_CHAR (d, 'w') week,
TO_CHAR (d, 'd') day_no,
TO_CHAR (d, 'Day') Day
FROM t
WEEK DAY_NO DAY
---- ------ ----------
1 5 Donnerstag
1 row selected.
It is possible to make it how do you want it, independent of NLS_TERRITORY:
可以按照您想要的方式进行设置，与 NLS_TERRITORY 无关：
WITH t AS (SELECT TO_DATE ('07-08-2016', 'dd-mm-rrrr') d FROM DUAL)
SELECT trunc(TO_CHAR (d+1, 'j')/7)-trunc(TO_CHAR (trunc(trunc(d,'mm'),'IW')+1, 'j')/7)+1 week,
TO_CHAR (d, 'd') day_no,
TO_CHAR (d, 'Day') Day
FROM t
WEEK DAY_NO DAY
---------- ------ ----------
2 1 Sonntag
1 row selected.
but it will be funny, when the first day of month is Saturday. You will have 6-th week in the month.
但这会很有趣，当月的第一天是星期六。本月您将有第 6 周。
WITH t AS (SELECT TO_DATE ('30-11-2014', 'dd-mm-rrrr') d FROM DUAL)
SELECT trunc(TO_CHAR (d+1, 'j')/7)-trunc(TO_CHAR (trunc(trunc(d,'mm'),'IW')+1, 'j')/7)+1 week,
TO_CHAR (d, 'd') day_no,
TO_CHAR (d, 'Day') Day
FROM t
WEEK DAY_NO DAY
---------- ------ ----------
6 1 Sonntag
1 row selected.
