Converting date and week # to monday date? 4

[Will192]

I have the year and the number for the week of the year. What would be the SQL to convert these two numbers to the monday date of that week?

Example:

year 2005
week #3

I want to return the date 2005-01-10

(I am assuming that is considered the third week cause the first was on a Saturday)

Thanks in advance for any responses to this post.

 

[mercwrought]

Ok I have an idea you can try this

declare @week int,
	@year int

set @week = 3
set @year = 2005

select (case(cast(datename(dw, dateadd(d,(@week*7)-1,'1/1/'+cast(@year as varchar(4))))as varchar(10)))
	when 'Monday' then dateadd(d,(@week*7)-1,'1/1/'+cast(@year as varchar(4)))
	when 'Tuesday' then dateadd(d,(@week*7)-2,'1/1/'+cast(@year as varchar(4)))
	when 'Wednesday' then dateadd(d,(@week*7)-3,'1/1/'+cast(@year as varchar(4)))
	when 'Thursday' then dateadd(d,(@week*7)-4,'1/1/'+cast(@year as varchar(4)))
	when 'Friday' then dateadd(d,(@week*7)-5,'1/1/'+cast(@year as varchar(4)))
	when 'Saturday' then dateadd(d,(@week*7)-6,'1/1/'+cast(@year as varchar(4)))
	when 'Sunday' then dateadd(d,(@week*7),'1/1/'+cast(@year as varchar(4)))
	else '' end)

it should work.
You will have to fiddle with it depending on if you consider the start of the week as Monday or Sunday.

 

[chamilz]

Hi...
I found this code in this forum and it works perfectly..
Sorry..couldnt remeber original author..


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)

/Chamil

 

[Will192]

I got this answer on another forum. It works and I have already implemented it on the production server.

SELECT DATEADD(d, 2 - DATEPART(dw, DATEADD(wk, @week-1, CONVERT(char(4), @year) + '0101')), DATEADD(wk, @week-1, CONVERT(char(4), @year) + '0101'))

 

[vongrunt]

I found this code in this forum and it works perfectly..
Sorry..couldnt remeber original author..

That would be me . There is one difference though:

- this code returns Nth Monday in year (returns 2005-01-17)
- Will192 wants Monday in Nth week of year (expected 2005-01-10)

I got this answer on another forum. It works and I have already implemented it on the production server.

Looks OK. One semi-warning: @@DATEFIRST must be set to 7. Probably not a problem at all (this is SQL2k/US default), unless someone previously fiddles with SET DATEFIRST within the scope.

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

 

[yksvaan]

If you were using European week standard (International Organization for Standardization), and your year states the "year of the week" instead of calendar year, you can get Monday with the following, no matter what DATEFIRST setting you have, there's example data. Works with years greater than 1899, though it's quite easy to change it to make it work with older years too:

SELECT myISOweekyear, myISOweek,
7*(myISOweek-1) + DATEADD(yy,myISOweekyear-DATEPART(yy,0),0)+ 3 - DATEDIFF(dd,0,DATEADD(yy,myISOweekyear-DATEPART(yy,0),0)+3)%7 AS Monday_datetime
FROM (
SELECT 2005 AS myISOweekyear, 50 AS myISOweek
UNION
SELECT 2005 AS myISOweekyear, 1 AS myISOweek
UNION
SELECT 2004 AS myISOweekyear,1 AS myISOweek
UNION
SELECT 2004 AS myISOweekyear,53 AS myISOweek
) D

sql is using the best datetime practices of member donutman

Cheers

[blue]Backup system is as good as the latest recovery[/blue]