oracle Oracle周计算问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2283663/
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
Oracle week calculation issue
提问by craig
I am using Oracle's to_char() function to convert a date to a week number (1-53):
我正在使用 Oracle 的 to_char() 函数将日期转换为周数 (1-53)：
select pat_id,
pat_enc_csn_id,
contact_date,
to_char(contact_date,'ww') week,
...
the 'ww' switch gives me these values for dates in January of this year:
“ww”开关为我提供了今年 1 月日期的这些值：
Date Week
1-Jan-10 1
2-Jan-10 1
3-Jan-10 1
4-Jan-10 1
5-Jan-10 1
6-Jan-10 1
7-Jan-10 1
8-Jan-10 2
9-Jan-10 2
10-Jan-10 2
11-Jan-10 2
12-Jan-10 2
a quick look at the calendar indicates that these values should be:
快速查看日历表明这些值应该是：
Date Week
1-Jan-10 1
2-Jan-10 1
3-Jan-10 2
4-Jan-10 2
5-Jan-10 2
6-Jan-10 2
7-Jan-10 2
8-Jan-10 2
9-Jan-10 2
10-Jan-10 3
11-Jan-10 3
12-Jan-10 3
if I use the 'iw' switch instead of 'ww', the outcome is less desirable:
如果我使用“iw”开关而不是“ww”，则结果不太理想：
Date Week
1-Jan-10 53
2-Jan-10 53
3-Jan-10 53
4-Jan-10 1
5-Jan-10 1
6-Jan-10 1
7-Jan-10 1
8-Jan-10 1
9-Jan-10 1
10-Jan-10 1
11-Jan-10 2
12-Jan-10 2
Is there another Oracle function that will calculate weeks as I would expect or do I need to write my own?
是否有另一个 Oracle 函数可以按照我的预期计算周数，还是我需要自己编写？
EDIT
编辑
I'm trying to match the logic used by Crystal Reports. Each full week starts on a Sunday; the first week of the year starts on whichever day is represented by January 1st (e.g. in 2010, January 1st is a Friday).
我正在尝试匹配 Crystal Reports 使用的逻辑。每个完整的星期都从星期日开始；一年中的第一周从 1 月 1 日所代表的那一天开始（例如，在 2010 年，1 月 1 日是星期五）。
采纳答案by craig
Based on this question, How do I calculate the week number given a date?, I wrote the following Oracle logic:
基于这个问题，如何计算给定日期的周数？，我写了如下Oracle逻辑：
CASE
--if [date field]'s day-of-week (e.g. Monday) is earlier than 1/1/YYYY's day-of-week
WHEN to_char(to_date('01/01/' || to_char([date field],'YYYY'),'mm/dd/yyyy'), 'D') - to_char([date field], 'D') > 1 THEN
--adjust the week
trunc(to_char([date field], 'DDD') / 7) + 1 + 1 --'+ 1 + 1' used for clarity
ELSE trunc(to_char([date field], 'DDD') / 7) + 1
END calendar_week
回答by Patrick
When using IW, Oracle follows the ISO 8601 standard regarding week numbers (see http://en.wikipedia.org/wiki/ISO_8601). That is the same standard than the one we generally use in Europe here.
使用 IW 时，Oracle 遵循有关周数的 ISO 8601 标准（请参阅http://en.wikipedia.org/wiki/ISO_8601）。这与我们在欧洲通常使用的标准相同。
Your problem is also mentioned on the Oracle forum: http://forums.oracle.com/forums/thread.jspa?threadID=947291and http://forums.oracle.com/forums/message.jspa?messageID=3318715#3318715. Maybe you can find a solution there.
Oracle 论坛上也提到了您的问题：http: //forums.oracle.com/forums/thread.jspa? threadID= 947291和http://forums.oracle.com/forums/message.jspa?messageID=3318715# 3318715。也许你可以在那里找到解决方案。
回答by akoman
I know this is old, but still a common question.
我知道这是旧的，但仍然是一个常见的问题。
This should give you the correct results in the smallest amount of effort:
这应该以最少的努力为您提供正确的结果：
select pat_id,
pat_enc_csn_id,
contact_date,
to_char(contact_date + 1,'IW') week,
...
回答by Jens Schauder
Since it looks like you are using yourown special definition of the week number you'll need to write your own function.
因为它看起来像您使用您的周数的自己的特殊定义，你需要编写自己的功能。
It might be helpful that NLS_TERRITORY affects the day with which a week starts as used by the D Format Model
NLS_TERRITORY 影响 D 格式模型使用的一周开始的日期可能会有所帮助
see also:
也可以看看：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#SQLRF00210
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#SQLRF00210
and
和
