I’m working on a little home project right now that does some examination of the current day of the week against a database table containing events by year and week number.
Curious to know if my event for this week would run as scheduled, I did some checking. Microsoft SQL Server thinks that we’re currently week 53 of 2015, instead of week 1 of 2016.
But Outlook is telling me we’re in week 1 of 2016, because January 1st is on Friday! How can this be?
My workaround was to duplicate the event for week 1 of 2016 in a new record for week 53 of 2015, but the reason seems worth exploring.
Based on various articles I’ve read, including this post I found on Stack Overflow, I’m led to believe that SQL Server is smart enough to know the difference between the last days of the current year and the first days of the new in the context of the week that bridges the two years.
Any days in the current year that extend beyond week 52 (starting on Sunday) are called week 53, and any days in the coming year in the same calendar week (up through Saturday) are considered week 1. The next Sunday starts week 2.
Example: This year, December 27th fell on Sunday, and January 1st is on Friday. So, from a SQL Server perspective, Sunday, December 27th through Thursday, December 31st are week 53; Friday, January 1st and Saturday, January 2nd are week 1, and Sunday, January 3rd through 9th are a full week 2.
So my week 53 workaround should work as long as the event day is before January 1st.
SQL Server also has the concept of the ISO WEEK datepart. Ask your doctor if it’s right for you.