CODEWITHSUNDEEP
sqloracle-database
omdurg
omdurg

Reputation: 330

How to find max ISO week of a year

Is there a way in which we could find the last week number given a year.

For example, for year 2018, last week number of 2018 is 52.

For year 2015, last week number is 53.

Upvotes: 2

Views: 710

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

If you are referring to ISO weeks, then you can use definitions around ISO weeks. The first week of a year always includes Jan 4th. Hence, the last week of the year always includes Jan 4 - 7 days, which is Dec 28. So:

select to_char(to_date(year || '-12-28', 'YYYY-MM-DD'), 'IW')

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

Last week always stays in month December which has always 31 days. Let's generate 31 rows to search for the greatest value, then :

select max(
       to_char(to_date(:i_year||'-12-'||lpad(level,2,'0'),'yyyy-mm-dd'),'iw')
       ) as last_ISO_week
  from dual
connect by level <= 31

Upvotes: 0

Atif
Atif

Reputation: 2210

You can use the below code using both Iso week and normal week.

SELECT 
CASE WHEN WEEK_NUM = ISO_WEEK_NUM 
THEN ISO_WEEK_NUM 
ELSE WEEK_NUM -ISO_WEEK_NUM 
END AS LAST_WEEK FROM 
(
    SELECT 
    to_number(to_char(to_date('12/31/2015','MM/DD/YYYY'),'WW')) week_num, 
    to_number(to_char(to_date('12/31/2015','MM/DD/YYYY'),'IW')) iso_week_num
    FROM dual
);

Upvotes: 1

Related Questions