Get Date From Week nbr and Year 2

[ScrewBalll]

Hi there,

I'm hoping someone can help me with a Date problem in SQL.

From one database I am being passed two fields, a Year and a WeekNbr (1-52). From these two fields I have to construct a date for the begining of the week, ie Monday.

For example if I'm passed the year 2005 and week 1, I want to come up with the date 3rd Jan 2005 as that was the first Monday of the year.

Anyone got any ideas?? I can use the Datepart function to get from a date to the week, year etc but I cant see a way of doing it in reverse. Thanks.

 

[hmckillop]

One of the first things you need to do is to set the DateFirst to be 1, this sets the start of the week to be handled as Monday (the default for US English is Sunday 7)

declare @weeknbr int, @year int, @DateInWeek datetime
SET DATEFIRST 1
SELECT @weeknbr =1, @year=2005
SELECT @DateInWeek = Dateadd(ww, @weeknbr, '01/01/' + convert(varchar, @year))
SELECT CASE DatePart(dw, @DateInWeek ) 	
		WHEN 1 THEN @DateInWeek 
		WHEN 2 THEN Dateadd(d, -1, @DateInWeek )
		WHEN 3 THEN Dateadd(d, -2, @DateInWeek )
		WHEN 4 THEN Dateadd(d, -3, @DateInWeek )
		WHEN 5 THEN Dateadd(d, -4, @DateInWeek )
		WHEN 6 THEN Dateadd(d, -5, @DateInWeek )
		WHEN 7 THEN Dateadd(d, -6, @DateInWeek )
	end as FirstDayOfWeek

a bit untidy but I am sure you can sort something from this.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[ScrewBalll]

Thanks for your help,

I ended up using:
DATE =
select (dateadd(wk,WEEK ,0) + dateadd(yy,(YEAR-1900),0))
- 5 - datepart(dw,(dateadd(year,(YEAR-1900),0)))

The minus 5 was just to set the day to Monday. I know its not pretty but it seems to get the job done.

Cheers for your help

 

[vongrunt]

IMO you can set it to Jan 07 then find first previous Monday. Something like:

declare @weeknbr int, @year int
SELECT @weeknbr =1, @year=2005
SET DATEFIRST 1
select dateadd(ww, @weeknbr-1+datediff(ww, 0, convert(varchar, @year) + '-01-07'), 0)

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba