Date Fucntion in Cognos

[Jay_p]

Hi Guys,

I have a field which returns me Year and week such as '201352', I need to find a way to convert this into '20131226' calculating day based on year and week. Can someone help?

[BigChris]

What determines the 20131226 from the 201352? Is it the first Monday in the week? (Haven't checked the 26th was a Monday, but you see what I mean).

[Jay_p]

Thanks for your reply, based on the week field which is the last two integers in that data item. Basically I am trying to define the days ex: week starts on a Sunday ending on Saturday

[BigChris]

Ok - I can't write it down at the moment, but what you'll need to do it calculate the start date (the first Sunday in the year), then add number of weeks * 7 days to that date. I'll see if I can construct that for you and I'll post it if I can. Obviously there might be a better way...those with bigger/better brain than mine might be able to suggest something in the meantime.

[bdbits]

I am probably over-thinking this and there is a simpler way, and I did not have test data handy, and there is probably some edge case I am missing, but... here is a shot at it.

I think this would give you the first Sunday of the year for [MyDataItem] where the first four characters are the year:
_add_days(cast('01-01-' + substring([MyDataItem],1,4),date),7-(_day_of_week(cast('01-01-'+substring([MyDataItem],1,4),date),1)))

Then calculate how many days to offset this using the number of weeks from your data item:
7*(cast(substring([MyDataItem],5,2),integer)-1)

Combined:
_add_days(_add_days(cast('01-01-' + substring([MyDataItem],1,4),date),7-(_day_of_week(cast('01-01-'+substring([MyDataItem],1,4),date),1))),7*(cast(substring([MyDataItem],5,2),integer)-1))

Oh dear that looks messy, there must be a better way. If you insist on using this, you will want to test it. Heavily. :D

[bdbits]

I should have mentioned, if you need to do this sort of thing a lot, I would put this kind of thing in a date table in my data source, or if not there then as a query subject in Framework Manager. You can put all the messy datetime calculations in there and reference them as needed.

[MFGF]

I should have mentioned, if you need to do this sort of thing a lot, I would put this kind of thing in a date table in my data source, or if not there then as a query subject in Framework Manager. You can put all the messy datetime calculations in there and reference them as needed.

Agree! Agree! Agree! :)

[Jay_p]

Thank you very much for your expert analysis, very much appreciated