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?
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).
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
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.
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
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.
Quote from: bdbits on 16 Mar 2015 05:25:17 PM
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! :)
Thank you very much for your expert analysis, very much appreciated