Convert datepart week into smalldatetime range

[Karin3]

I need to convert week numbers into the corresponding date range. The reverse is quite simple

select datepart(week, 'Sep 12 2001')

This will state that for
Sep 12 2001 the week number is 37. How can I get the date range for week number?

 

[tlbroadbent]

Here is a T-SQL snippet that does what you want. In SQL 7, you could use this in a stored procedure. In SQL 2000, it could become a UDF. Hopefully, someone can suggest something easier.

This example determines for the start and end dates of week 20.

declare @wk int, @dt smalldatetime, @dw int

set @wk=20
set @dt=dateadd(week,@wk,'01/01/' + str(year(getdate())))
Set @dw=datepart(dw, @dt)

Select dateadd(d,1-@dw,@dt) AS StartDate, dateadd(d,7-@dw,@dt) As EndDate

This code depends on January 1 being in the first week. If it is not you'll need to do some adjusting. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[Karin3]

Thanx for the tip, I'll give it a try.
Karin