# how to find the last date from the week number as input

I need to find the last date of week when i pass year and week number as input in my stored procedure.

week starts on monday and last day of week will be friday.

Database - IBM db2 9.7 platform - windows

How to do it?

Based on your comment this seems to be an ISO week number, but the logic is the same for any other numbering schema: take the year and convert it to the first day of the first week of this year, then add the `(weeknumber -1) * 7` to get the starting day of the week and add 4 to get Friday.

I don't know about DB2 syntax, but a quick check revealed that TRUNC on dates and NEXT_DAY are supported, so it should be similar to:

``````trunc(cast(cast(year as char(4)) || '-01-04' as date), 'IW') -- first day of the first week based on ISO
+ (week-1)*7                                                 -- Monday of the  actual week number
+ 4                                                          -- Monday --> Friday
``````

There's a function for that (`NEXT_DAY`):

``````NEXT_DAY(DATE(CAST((<year>*1000 + (<week>-1)*7+1) AS CHAR(7)) -1, 'FRI')
``````

Thanks dnoeth for the tip on `-1`!

• @Ziggy Crueltyfree Zeitgeister.. your answer is not working.. it is giving me wrong result. and please tell me why u multiply year with 1000 Apr 29, 2016 at 9:20
• @Govind the function `DATE()` takes a char(7) parameter in the form of YYYYDDD, so can be crafted by multiplying year * 1000 + day, and casting the result to a CHAR(7), e.g. year=2016 and day=75 => 2016*1000+75 => 2016075. What is not working? What error do you get? Apr 29, 2016 at 9:23
• so what does it mean here.. it does not works in db2 Apr 29, 2016 at 9:26

Finally I queried the answer of this question -

``````SELECT NEXT_DAY(CAST(CAST(2016 AS CHAR(4)) || '-01-01' AS DATE)+ ((4-1)*7-1) DAYS,'FRI')
FROM SYSIBM.SYSDUMMY1
``````

Sharing here so other could also learn.