Link to home
Start Free TrialLog in
Avatar of tonMachine100
tonMachine100

asked on

number of week in year - oracle syntax

I've got some sql code which lists all the days within the last 4 weeks of the report run date.
I now need to add another field in which calculates the week number (in the year). The to_char(d, 'ww') function doesnt quite fit my requirements - i need a week to run from Mon-Sun, not Sat-Fri.
Fig1. is the current output (using the code below), Fig2. is the desired output.
Any help with the code is appreciated output.xls
SELECT d asm_date, TO_CHAR(d, 'Day') day_of_week, to_char(d, 'ww') week_no
  FROM (SELECT NEXT_DAY(TRUNC(SYSDATE - 1), 'Sunday') - LEVEL + 1 d
          FROM DUAL
        CONNECT BY LEVEL <= 28)
ORDER BY 1

Open in new window

Avatar of Jacobfw
Jacobfw
Flag of Canada image

Like this (just added 2 days for the week calculation):

SELECT d asm_date, TO_CHAR(d, 'Day') day_of_week, to_char(d-2, 'ww') week_no
  FROM (SELECT NEXT_DAY(TRUNC(SYSDATE - 1), 'Sunday') - LEVEL + 1 d
          FROM DUAL
        CONNECT BY LEVEL <= 28)
ORDER BY 1
Gets a little funky around the yearend though.  What do you want for the last week/first week of the year?
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial