Sql-server – Get dates from a week number in T-SQL


In Microsoft SQL Server, I have a week number

(from DATEPART(wk, datecol)) 

But what I would like to do is turn this back into the date span for that week.

For example,


yields 10. I would like to derive 3/1/2009 and 3/7/2009 from this number.

Is this possible?

Best Solution

Quassnoi's answer works, but kind of leaves you on the hook for cleaning up the dates if they are dates in the middle of the day (his start of week leaves you one day earlier than you need to be if you use a time in the middle of the day -- you can test using GETDATE()).

I've used something like this in the past:

   CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, DATECOL), DATECOL)), 101),
   CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, DATECOL) - 6, DATECOL)), 101)

A side benefit of this is that by using @@DATEFIRST you can handle nonstandard week starting days (the default is Sunday, but with SET @@DATEFIRST you can change this).

It seems crazy that simple date manipulation in SQL Server has to be this arcane, but there you go...