-1

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?

0
2

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
0

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!

3
  • @Ziggy Crueltyfree Zeitgeister.. your answer is not working.. it is giving me wrong result. and please tell me why u multiply year with 1000
    – Govind
    Apr 29 '16 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 '16 at 9:23
  • so what does it mean here.. it does not works in db2
    – Govind
    Apr 29 '16 at 9:26
0

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.