ISO Week in SQL Server

First let’s take a look at what ISO week is, from WikiPedia:
Week date representations are in the format as shown below.
YYYY-Www or YYYYWww
YYYY-Www-D or YYYYWwwD

[YYYY] indicates the so-called ISO year which is slightly different than the calendar year (see below). [Www] is the week number prefixed by the letter ‘W’, from W01 through W53. [D] is the weekday number, from 1 through 7, beginning with Monday and ending with Sunday. This form is popular in the manufacturing industries.
There are mutually equivalent definitions for week 01:

• the week with the year’s first Thursday in it,
• the week with 4 January in it,
• the first week with the majority (four or more) of its days in the starting year, and
• the week starting with the Monday in the period 29 December – 4 January.

If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year.
The week number can be described by counting the Thursdays: week 12 contains the 12th Thursday of the year.
The ISO year starts at the first day (Monday) of week 01 and ends at the Sunday before the new ISO year (hence without overlap or gap). It consists of 52 or 53 full weeks. The ISO year number deviates from the number of the calendar year (Gregorian year) on a Friday, Saturday, and Sunday, or a Saturday and Sunday, or just a Sunday, at the start of the calendar year (which are at the end of the previous ISO year) and a Monday, Tuesday and Wednesday, or a Monday and Tuesday, or just a Monday, at the end of the calendar year (which are in week 01 of the next ISO year). For Thursdays, the ISO year number is always equal to the calendar year number.
Examples:

• 2008-12-29 is written “2009-W01-1″
• 2010-01-03 is written “2009-W53-7″

You can read more about ISO week here: http://en.wikipedia.org/wiki/ISO_week_date

Sometimes you need to show the ISO week in SQL server but there was no built in way to calculate it until SQL server 2008 was released. In SQL Server 2000/2005 you could use the user defined function ISOweek which was in the SQL Server books on line.
Here is what the function looks like

Now run the following query on SQL server 2000 and up

If you are running SQL server 2008 then you can use DATEPART and the datepart argument isowk. Run the select statement below to see the result

As you can see here also SQL Server’s wk part returns 53 while isowk returns 1

I have also added parts of this to the wiki here: ISO Week In SQL Server

You have only $50 left and you can buy two DVDs or one SQL book, what do you do? I would buy the book but not every person has the same idea of a fun time. This is the reason why I present you with a bunch of links to articles which will give you very good info. some of this you won’t be able to find in a book anyway.

The curse and blessings of dynamic SQL. How you use dynamic SQL, when you should - and when you should not.

Arrays and Lists in SQL Server. Several methods on how to pass an array of values from a client to SQL Server, and performance data about the methods. Two versions are available, one for SQL 2005 and one for SQL 2000 and earlier.

Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background. Two articles on error handling in SQL Server.

The ultimate guide to the datetime datatypes
The purpose of this article is to explain how the datetime datatypes work in SQL Server, including common pitfalls and general recommendations.

Stored procedure recompiles and SET options
Using stored procedures is generally considered a good thing. One advantage of stored procedures is that they are precompiled. This means that at execution time, SQL Server will fetch the precompiled procedure plan from cache memory (if exists) and execute it. This is generally faster than optimizing and compiling the code for each execution. However, under some circumstances, a procedure needs to be recompiled during execution.

Do You Know How Between Works With Dates?
article explaining why it can be dangerous to use between with datetime data types

How Are Dates Stored Internally In SQL Server?
Article explaining how datetimes are actually stored internally

Three part deadlock troubleshooting post, a must read if you want to understand how to resolve deadlocks.
Deadlock Troubleshooting, Part 1
Deadlock Troubleshooting, Part 2
Deadlock Troubleshooting, Part 3

SQL Server 2005 Whitepapers List
A list of 29 different SQL Server 2005 Whitepapers

Keep a check on your IDENTITY columns in SQL ServerThis article shows you how to keep an eye on your IDENTITY columns and find out before they run out of values, and fail with an arithmetic overflow error.

Character replacements in T-SQL
Quite often SQL programmers are left with the dirty job of working with badly formatted strings mostly generated from external sources. Typical examples are badly structured date values, social security numbers with misplaced hyphens, badly formatted phone numbers etc. When the data set if small, in many cases, one can easily fix by a one time cleanup code snippet, but for larger sets one will need more generalized routines.