ISO8601 Convert Week Date to Calendar Date

Question

I'm trying to determine the day of a given a week date given (i.e. YYYY-W##) in the ISO 8601 format. My end goal is to convert an ISO 8601 Week Date to an ISO 8601 Calendar Date. I need to do this in TSQL (I'm working with SQL Server 2005), I'm not sure if there's anything already built-in to SQL Server 05 that allows this, but it would help to see an example in another language or in generic pseudocode

UPDATE:

Sorry if the structure of my question is confusing. Basically, I have a ISO8601 week date that I'm trying to convert to ISO8601 Calendar Date.

Examples (from Wikipedia)

ISO 8601 Week Date: 2012-W02 (YYYY-W##)

Convert to...

ISO 8601 Calendar Date: 2012-01-09 (YYYY-MM-DD)

Since the day component isn't given in my week date example, the first day of the week can be assumed.

Answer 1

Given a proper ISO week function from the CREATE FUNCTION documentation (with some adjustments for all the bad habits that are in there, IMHO):

CREATE FUNCTION dbo.ISOWeek 
( 
    @dt SMALLDATETIME 
) 
RETURNS TINYINT 
AS 
BEGIN 
    DECLARE @ISOweek TINYINT;

    SET @ISOweek = DATEPART(WEEK, @dt) + 1 
        -DATEPART(WEEK, RTRIM(YEAR(@dt)) + '0104');

    IF @ISOweek = 0 
    BEGIN 
        SET @ISOweek = dbo.ISOweek 
        ( 
            RTRIM(YEAR(@dt)-1)+'12'+RTRIM(24 + DAY(@dt)) 
        ) + 1;
    END 

    IF MONTH(@dt) = 12 AND DAY(@dt) - DATEPART(DAYOFWEEK, @dt) >= 28 
    BEGIN 
        SET @ISOweek = 1; 
    END 

    RETURN(@ISOweek);
END 
GO

We can create a table like this:

CREATE TABLE dbo.ISOWeekCalendar
(
    [Date] SMALLDATETIME PRIMARY KEY,
    ISOWeekNumber TINYINT,
    [Year] INT,
    ISOWeek CHAR(8)
);
CREATE UNIQUE INDEX iw ON dbo.ISOWeekCalendar(ISOWeek);

We can populate it with data from any range of years, this uses ISO weeks 1-52 for 2000 - 2029:

DECLARE @StartDate SMALLDATETIME,
        @EndDate   SMALLDATETIME;

SELECT @StartDate = '20000102',
       @EndDate   = '20291229';

INSERT dbo.ISOWeekCalendar([Date])
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)+1) n
 = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, @StartDate)
 FROM sys.all_objects AS s1
 CROSS JOIN sys.all_objects AS s2
 ORDER BY s1.[object_id];

Now we can update the data.

-- delete all non-Mondays:
SET DATEFIRST 1;
DELETE dbo.ISOWeekCalendar WHERE DATEPART(WEEKDAY, [Date]) <> 1;

-- put the proper ISO week number:
UPDATE dbo.ISOWeekCalendar SET ISOWeekNumber = dbo.ISOWeek([Date]);

-- put the year:
UPDATE dbo.ISOWeekCalendar SET [Year] = DATEPART(YEAR, [Date]);

-- update to the correct year for fringe days:
UPDATE dbo.ISOWeekCalendar SET [Year] = [Year] + 1 
  WHERE ISOWeekNumber = 1 AND MONTH([Date]) = 12;

-- finally, build the calculated value for YYYY-W##:
UPDATE dbo.ISOWeekCalendar 
  SET ISOWeek = RTRIM([Year]) + '-W' + RIGHT('0' + RTRIM(ISOWeekNumber), 2);

Note that the above only has to be done once. Now we can run a very simple query given our input:

SELECT [Date] FROM dbo.ISOWeekCalendar WHERE ISOWeek = '2012-W02';

Results:

Date
-------------------
2012-01-09 00:00:00

We can even create a function that does this:

CREATE FUNCTION dbo.ISOWeekDate(@ISOWeek CHAR(8))
RETURNS SMALLDATETIME
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT [Date] FROM dbo.ISOWeekCalendar
    WHERE ISOWeek = @ISOWeek);
END
GO

And a function that goes the other way:

CREATE FUNCTION dbo.ISOWeekFromDate(@Date SMALLDATETIME)
RETURNS CHAR(8)
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT TOP (1) ISOWeek FROM dbo.ISOWeekCalendar
    WHERE [Date] <= @Date
    ORDER BY [Date] DESC);
END
GO

Query:

SELECT dbo.ISOWeekDate('2012-W02'), dbo.ISOWeekFromDate('20120110');

Results:

-------------------    --------
2012-01-09 00:00:00    2012-W02

Yes, it is a little more up-front work than a complicated query, but I prefer ease of use and clearer query semantics.

Answer 2

Try the following solution:

declare 
    @date varchar(10)
    ,@convertedDate datetime
    ,@wk int
    ,@yr int

set @date = '2012-W02';

set @yr = parsename(replace(@date, '-W', '.'), 2)
set @wk = parsename(replace(@date, '-W', '.'), 1)

set @convertedDate = convert(varchar(10), dateadd(week, @wk, dateadd (year, @yr-1900, 0)) - 5 - datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0))), 121)

select 
    'Year' = @yr
    ,'Week' = @wk
    ,'Date' = @convertedDate

Output:

-----------------------------------------
| Year | Week |         Date            |
-----------------------------------------
| 2012 |   2  | 2012-01-09 00:00:00.000 |
-----------------------------------------