Oracle周计算问题 [英] Oracle week calculation issue

问题描述

我正在使用Oracle的to_char（）函数将日期转换为星期数（1-53）：

I am using Oracle's to_char() function to convert a date to a week number (1-53):

select  pat_id, 
    pat_enc_csn_id, 
    contact_date, 
    to_char(contact_date,'ww') week,
    ...

ww开关为我提供了今年1月的日期的这些值：

the 'ww' switch gives me these values for dates in January of this year:

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

如果我使用 iw开关而不是 ww，则结果不太理想：

if I use the 'iw' switch instead of 'ww', the outcome is less desirable:

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

是否有另一个Oracle函数可以按我期望的那样计算周数？还是需要自己编写？

Is there another Oracle function that will calculate weeks as I would expect or do I need to write my own?

编辑

我正在尝试匹配Crystal Reports使用的逻辑。每个整周从星期日开始。一年的第一周从1月1日代表的哪一天开始（例如，在2010年，1月1日是星期五）。

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).

推荐答案

基于此问题，如何计算给定日期的星期数？，我编写了以下Oracle逻辑：

Based on this question, How do I calculate the week number given a date?, I wrote the following Oracle logic:

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

这篇关于Oracle周计算问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！