CODEWITHSUNDEEP
sqloracle-database
Munesh
Munesh

Reputation: 1569

How to determine week of a quarter in Oracle query

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.

Upvotes: 0

Views: 2309

Answers (1)

MT0
MT0

Reputation: 168741

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).

Upvotes: 2

Related Questions