How to find max ISO week of a year

user_13200216 0
Viewed 203

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.

oracle sql
3 Answers

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')
user_11440350

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
);
user_109082740

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
user_58413060
Related