ISOWEEK 52 Weeks in the Year
Stardate 12:20:2011 Week 51 SSC.Com Web Blog, Planet Earth. With only approximately two weeks left of the Year 2011, I believed we were rapidly approaching the 52nd week, signifying the completed cycle of 52 weeks in a year. At least that’s what I thought.
Yes, we grew up learning that is takes 365 days for the Earth to complete a full orbit around the sun. Or, more accurately, Earth takes about 365 1/4 days (365.2425 days) to revolve around or orbit the Sun. The 1/4 of a day, is why we have a leap year (a year with an extra day [which is always February 29th]) every 4 years. OK, fair enough, and indeed 2012 will be a leap year! Happy Birthday to all those folks who were born on February 29th!
Nonetheless, it seemed no matter whether a leap year, 365 ¼ days, a small shift of the Earth’s axis, or minor adjustment on the atomic clock, 52 weeks in a year was a sure certainty! Right? That’s how we calculate our yearly salary after all – if you’re a consultant its $X.xx per hour x 40 hours per week x 52. Or, to get your weekly salary as a W2 employee (very weakly ) we divide the annual pay divided by 52 weeks. So, why all the fuss about a week here or there?
Well, I was recently asked by a client to calculate the number of records in a table over this past year, broken down by week. No problem! I had a handy little T-SQL script, using of course, the Date function, Datepart (WEEK).
We already know that the Datepart as defined by the latest entry in the MSDN 2008 R2 library on T-SQL DATEPART, the part of date (a date or time value) for which an integer will be returned. By examining the article, we see a table listing all the valid datepart arguments such as Year, Month, and in this case Week. Week can be specified by using the argument week, wk, or ww –for example, DATEPART(week, getdate())
Now, if you run a simple statement using either of the above “week” arguments Select (wk,getdate()) this very week, or more to the point Select (wk,’2011-12-20’), it will return the week number, 52. Fifty-two? You mean, it’s already the end of the year? Don’t we have at least one week left until the New Year? Shouldn’t this be the fifty-first (51st) week of the year? Not if there are fifty-three – 53 – weeks of the year. Say what? Yeah, I know. I guess that’s why they call the date part “arguments”
According to this handy chart, Week Numbers for 2011, there are indeed 52 weeks in the year, and this week, the week of December 19, 2011, is indeed the 51st week. But not necessarily according to SQL Server.
What if the first and last weeks of the year have less than 7 days since Sunday is considered the start of the week. This then means there could be 53 weeks in the year.
So, what does this mean for all our queries and reports? In most cases, this means that January 1 is always week number 1 of the year, and the following Sunday starts week 2. We normally would consider Sunday the first day of the beginning of the week. The easiest way to return Day Of Week using T-SQL is to use DATEPART function: SELECT DATEPART(weekday,GetDate()) or dw (for day of the week).
Using DATEPART with dw (weekday) argument will return an integer value between 1 and 7. The Date part function with weekday parameter value returns day of the week based on the specified date. Remember 1 = Sunday and 7 = Saturday. Now if I use the simple query Select @@DATEFIRST, the value for US English is 7 by default which indicates the first day of the week is Sunday. And, just to make sure, you can also run Select @@Language to see what the default setting is:
Now we have another argument. If according to my server, we’re using the default language setting us_english, and the first day of the week is ‘7’ meaning Sunday, then why does select datepart(weekday, '2011-12-18') , the 18th being a Sunday, return ‘1’ as the weekday number?
You can also find out the first day of the week for all languages with @@DATEFIRST and SET LANGUAGE here.
Well, back to the week number, and my query conundrum. It was a simple query to get a breakdown of the total number of records in an Audit table, by week. Here is a generic CTE query I wrote that was meant to do the job:
SELECT COUNT(*) AS No_of_AuditRecords, Week
FROM
(
SELECT DATEPART(WK,Create_Date), [WEEK] -- Using the DatePart argument 'WK'
-- did not get me the right week number. Use ISOWK instead.
FROM AuditRecordTable WITH(NOLOCK)
)ar
GROUP BY [Week] WITH ROLLUP --Rollup returns a row for the Grand Total of records
ORDER BY [WEEK]
Well, after some due diligence, it all came back to me about an organization called the International Organization for Standardization , not IOS, but better known to us as ISO (how’s that for standardization!?) If you’re interested in the history, you can click on the highlighted link to see the Wikipedia entry, but I’m not going to bore you here. You can also refer to ISO 8601 which addresses date and time data. More specific to our discussion, you can read more about ISO week here: http://en.wikipedia.org/wiki/ISO_week_date
Fortunate for whoever is using SQL Server 2008 and higher, there is a new option for the datepart() function. It can take an isowk parameter and tell you the ISO week number.
To get the official MSDN entry on the date functions, arguments and parameters in SQL Server 2008 R2, click here for the DatePart TSQL
I do acknowledge all you poor folks out there who are still stranded on SQL Server 2005 and below, so I provide you with two resource links that feature a cool function to get the ISOWeek. They also attempt to explain this little phenomena, but at least you can use the function. ISOWeek2000/2005 and dbo.udf_GetISOWeekNumberFromDate (But please, it’s time to get onto SQL Server 2008, as SQL 2012, RC0 recently released, is right around the corner!)
Finally, by using the ISO shorthand, if I simply replaced the argument ‘WK’, with ‘ISOWK’, in my example above, the problem is magically solved, and all is right with the planets and the heavens.
And, that’s a good thing, because otherwise, I’d have to wait a whole other week (for week number 53) to wish you all a Happy and Prosperous New Year 2012!
