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
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
Gets a little funky around the yearend though. What do you want for the last week/first week of the year?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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