基于此问题,我如何计算给定日期的星期数?,我编写了以下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