Oracle Week Number from a Date

Question

I am brand new to Oracle. I have figured out most of what I need but one field is driving me absolutely crazy. Seems like it should be simple but I think my brain is fried and I just can't get my head around it. I am trying to produce a Sales report. I am doing all kinds of crazy things based on the Invoice Date. The last thing I need to do is to be able to create a Week Number so I can report on weekly sales year vs year. For purposes of this report my fiscal year starts exactly on December 1 (regardless of day of week it falls on) every year. For example, Dec 1-7 will be week 1, etc. I can get the week number using various functions but all of them are based on either calendar year or ISO weeks. How can I easily generate a field that will give me the number of the week since December 1? Thanks so much for your help.

Answer 1

Forget about the default week number formats as that won't work for this specific requirement. I'd probably subtract the previous 1 December from invoice date and divide that by 7. Round down, add 1 and you should be fine.

select floor(
  (
  trunc(invoiceDate) - 
  case 
  -- if December is current month, than use 1st of this month
  when to_char(invoiceDate, 'MM') = '12' then trunc(invoiceDate, 'MM')
  -- else, use 1st December of previous year
  else add_months(trunc(invoiceDate, 'YYYY'), -1)
  end
  ) / 7
) + 1
from dual;