views:

6051

answers:

4

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,

SELECT DATEPART(wk, GETDATE())

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

Does anyone know if this is possible?

+1  A: 
SELECT DATECOL - DATEPART(weekday, DATECOL), DATECOL - DATEPART(weekday, DATECOL) + 7
Quassnoi
+3  A: 

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:

SELECT 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...

mwigdahl
Um, DATECOL is the user-defined column in the example. Quassnoi's code only relies on "DATEPART(weekday,", which is available on 2000.
entaroadun
Ah, good point. I messed that up. I'll edit accordingly.
mwigdahl
Where's the edit without using DATECOL? See my answer.
Brad
+2  A: 

You can set @WeekNum and @YearNum to whatever you want - in this example they are derived from the @datecol variable, which is set to GETDATE() for purposes of illustration. Once you have those values- you can calculate the date range for a week by using the following:

DECLARE @datecol datetime = GETDATE();
DECLARE @WeekNum INT
      , @YearNum char(4);

SELECT @WeekNum = DATEPART(WK, @datecol)
     , @YearNum = CAST(DATEPART(YY, @datecol) AS CHAR(4));

-- once you have the @WeekNum and @YearNum set, the following calculates the date range.
SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;
elindeblom
A: 
SELECT DATEADD(week, @weekNumber - 1, DATEADD(DAY, @@datefirst - DATEPART(weekday, CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01') - 6, CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01'))
Brad