How to determine week of a quarter in Oracle query

Question

I want to find the Week of a Quarter from a sql date in Oracle.

I tried below query to find the year, quarter and week. But the week field gives the 'Week of the month' not the 'Week of the quarter'

select to_char(sysdate, 'YYYY')|| '-Q' || to_char(sysdate, 'Q') || '-W' || >to_char(sysdate, 'w') as "Current Time" from dual;

Above query returns '2016-Q2-W3' as the date falls in the 3rd week of the month.

Say sysdate is '17th June, 2016' I am expecting result as

2016-Q2-W12

My Week range is (Sunday - Saturday)

Since the '17th June, 2016' comes under 12th week of the quarter, it should be W12.

Thanks in advance.

Your current query can be simplified to: select to_char(sysdate, 'YYYY-"Q"Q-"W"w') as "Current Time" from dual; — MT0
SELECT TRUNC( SYSDATE, 'Q' ) FROM DUAL gives 2016-04-01 which is a Friday. Does 2016-Q2-W1 run from Friday - Thursday? Or does it run Monday - Sunday? Or something else? — MT0

MT0 MT0 · Accepted Answer · 2016-06-17T22:11:47

This will get the week (Sunday - Saturday) of the quarter:

SELECT TO_CHAR( SYSDATE, 'YYYY-"Q"Q-"W"' )
     || ( 7 + TRUNC( SYSDATE + 1, 'IW' ) - TRUNC( TRUNC( SYSDATE, 'Q' ) + 1, 'IW' ) ) / 7;
         AS "Current Time"
FROM DUAL

Explanation:

You can find the Sunday which was either on-or-just-before a given date using NEXT_DAY( TRUNC( date_value ), 'SUNDAY' ) - 7 (which is dependant on the NLS_TERRITORY setting) or TRUNC( date_value + 1, 'IW' ) - 1 (which is shorter and not dependant on any settings).
TRUNC( date_value, 'Q' ) gives the date of the first day of the quarter containing the value date (i.e. either 1st January, 1st April, 1st July or 1st October).
Putting the two together, the Sunday on-or-just-before the first day of the quarter is given by TRUNC( TRUNC( date_value, 'Q' ) + 1, 'IW' ) - 1
Therefore, the number of days between the Sunday on-or-just-before the start of the quarter and the Sunday on-or-just-before a given date is: ( TRUNC( date_value + 1, 'IW' ) - 1 ) - ( TRUNC( TRUNC( date_value, 'Q' ) + 1, 'IW' ) - 1 ) - which can be simplified by cancelling the -1 terms.
The number of weeks difference is just that number divided by 7 (but gives a 0-indexed value and you want the week number of the quarter to be 1-indexed; you either add 1 week to the result or, prior to doing the division, add 7 days).

How to determine week of a quarter in Oracle query

1 Answers