Solved

# number of week in year - oracle syntax

Posted on 2011-10-04
382 Views
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
``````
0
Question by:tonMachine100

LVL 7

Expert Comment

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
0

LVL 7

Expert Comment

Gets a little funky around the yearend though.  What do you want for the last week/first week of the year?
0

LVL 73

Accepted Solution

are you counting whole weeks or partial weeks too?

for instance 2011 starts on a Saturday.  Is week 1 of 2011 a 2-day week?  Or, do you start counting weeks from the first full week of the year?

Are you looking for iso weeks?   if so, try iw format

SELECT d asm_date, TO_CHAR(d, 'Day') day_of_week, to_char(d, 'iw') week_no
FROM (SELECT NEXT_DAY(TRUNC(SYSDATE - 1), 'Sunday') - LEVEL + 1 d
FROM DUAL
CONNECT BY LEVEL <= 28)
ORDER BY 1
0

## Featured Post

### Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
how to add IIS SMTP to handle application/Scanner relays into office 365.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…