locked
Gregorian Calendar vs. Subscriber Calendar week calculations RRS feed

  • Question

  • User-965657988 posted

    I have a database with weeks represented as yyyyww (201323), I need to extract the information for the previous 13 weeks, so far so good but the difficulty is that our calendar is different from the Gregorian one, as the year starts at the end of June e.g. 2013/14 Week 1 is from Friday, 28<sup>th</sup> June 2013 to Thursday, 4<sup>th</sup> July 2013, so as you can gather not only the week numbers will not correspond to the standard ISO the week also do not start and end on the normal Sun-Sat or Mon-Sun.

    Is there anyone which come across to a similar issue and if so how can it be resolved as I do not have any idea, currently I do have a WHERE statement

     WHERE (((Tbl_Telephony_All.Week)= (SELECT Max(Tbl_Telephony_All.Week) AS [WeekNo] FROM Tbl_Telephony_All))) [\code] which would extract the information for the MAX ‘week’, however as I need to append the data for a 13 week period into another table I am a bit stock.

     

    Any help is very much appreciated.

    I believe this is the wrong forun so I re post this at http://answers.microsoft.com/en-us/office/forum/office_2010-access/gregorian-calendar-vs-subscriber-calendar-week/ac20dff2-6e13-45eb-bc9a-99015223120e?tm=1386844945291

    Thursday, December 12, 2013 5:32 AM

Answers

  • User-1199946673 posted

    the year starts at the end of June e.g. 2013/14 Week 1 is from Friday, 28<sup>th</sup> June 2013 to Thursday, 4<sup>th</sup> July 2013, so as you can gather not only the week numbers will not correspond to the standard ISO the week also do not start and end on the normal Sun-Sat or Mon-Sun.

    And when does the first week of 2014 start? And 2015, 2016 ect.....? Basicly, what is the logic behind this?

    I have a database with weeks represented as yyyyww (201323),

    In general, when you do this kind of calculations, you better store thes values as dates. So instead of storing 201323, I would store 2013/11/29 (the first day of week 23) Calculating a period of 13 weeks in SQL is just a matter of using the dataadd method The conversion from/to a weeknumber only takes place in your code (C# or VB.NET). But then you need the logic behind it

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 13, 2013 4:25 AM

All replies

  • User697462465 posted

    Hi,

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Best Regards,
    Terry Guo

    Thursday, December 12, 2013 9:53 PM
  • User-1199946673 posted

    the year starts at the end of June e.g. 2013/14 Week 1 is from Friday, 28<sup>th</sup> June 2013 to Thursday, 4<sup>th</sup> July 2013, so as you can gather not only the week numbers will not correspond to the standard ISO the week also do not start and end on the normal Sun-Sat or Mon-Sun.

    And when does the first week of 2014 start? And 2015, 2016 ect.....? Basicly, what is the logic behind this?

    I have a database with weeks represented as yyyyww (201323),

    In general, when you do this kind of calculations, you better store thes values as dates. So instead of storing 201323, I would store 2013/11/29 (the first day of week 23) Calculating a period of 13 weeks in SQL is just a matter of using the dataadd method The conversion from/to a weeknumber only takes place in your code (C# or VB.NET). But then you need the logic behind it

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 13, 2013 4:25 AM
  • User-1128292064 posted

    Hi, 

    We would definately need more information on this. As hans_v said it is better to use datetime instead of strings if you want to perform datetime calculations. What is the logic behind using such a calendar? What is the expectation? Do you need help writing the LINQ query to append data for a 13 week period?

    Thanks,

    Kunal

    Monday, December 23, 2013 6:14 PM