Gregorian Calendar vs. Subscriber Calendar week calculations[Answered] RSS

• • Reply
  vitordf

  Member

  1 Points

  16 Posts

  Gregorian Calendar vs. Subscriber Calendar week calculations

  Dec 12, 2013 05:32 AM|vitordf|LINK

  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^th June 2013 to Thursday, 4^th 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 [code] 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

  Our greatest weakness lies in giving up. The most certain way to succeed is always to try just one more time. - Thomas A. Edison
  
• • Reply
  Terry Guo -...

  Star

  12777 Points

  1635 Posts

  Re: Gregorian Calendar vs. Subscriber Calendar week calculations

  Dec 12, 2013 09:53 PM|Terry Guo - MSFT|LINK

  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

  
  
• • Reply
  hans_v

  All-Star

  25756 Points

  7014 Posts

  Re: Gregorian Calendar vs. Subscriber Calendar week calculations

  Dec 13, 2013 04:25 AM|hans_v|LINK

  vitordf

  the year starts at the end of June e.g. 2013/14 Week 1 is from Friday, 28^th June 2013 to Thursday, 4^th 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?

  vitordf

  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

• • Reply
  kunalmundada

  None

  0 Points

  4 Posts

  Re: Gregorian Calendar vs. Subscriber Calendar week calculations

  Dec 23, 2013 06:14 PM|kunalmundada|LINK

  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