Yes there is a Week 53

Yes there is actually a week 53, it's called a leap week, and happens approximately 71 times in 400 years. More specifically it happens whenever December 31/January 1st land on a Thursday, and a bunch of other cases. It's all part of the ISO 8601 standard. Meaning today is actually the first week of the year. Not a big surprise to anyone I would say or?

Then why was the report not showing the right information?

Who is out of Sync

I've tested this in Adobe ColdFusion as well as in Railo and both engines return the week date of todays date as week 1. Looking at my Moleskine it's also showing week 1, Google Calendar is also showing week 1, php is showing week 1, all good so far I would say.

I then decided to take a look at a couple of databases, specifically MSSQL and MySQL here I was rather surprised to see that only MSSQL in produced a week of 2 for todays date, MySQL produced a 1.

So who is wrong and who is right? I guess it depends on whether ISO 8601 is being followed or not. MySQL implements, that either Monday or Sunday is the beginning of a week, therefore since the year changes in the middle of the week. the first few days of the new year belong to week 53. This would mean that the first Monday/Sunday start the first week in the new Year.

Looking at the documentation for MSSQL Datepart I see the following:

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart...

Wait a sec thats not ISO 8601! They just decided that if January 1 lands on any day other than the starting day (monday/sunday) that its week 1.

So while "everyone" else is returning a week number of 1 for todays date, MSSQL is returning week 2!

When writing code that works with week numbers be aware of this discrepancy, if you haven't already come up against this before.

Happy Coding ...