Convert from a week of year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert from a week of year

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-11-19 : 11:10:35

I am using the DatePart(Week,AttendanceDate) function to get the week of the year for each spesific row.

How do I convert it back to a date range value e.g 1 = '2004/01/01 - 2004/01/07'?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 11:22:58

first thing that comes to mind:


declare @b datetime
set @b = getdate()
select 	@b,
	dateadd(d, -datepart(dw, @b )+2, @b) as monday,
	--dateadd(d, -datepart(dw, @b )+2, @b)+6 as sunday, -- this or the one below
	dateadd(d, 7-datepart(dw, @b )+1, @b) as sunday

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-19 : 11:25:40

do you want 1 - 6 or 1 - 7 for first week?
I did it first for a week = sun to sat so first week finishes on jan 3 but seems you want a week to be 7 days from jan 1?
so

select dateadd(d,(@w-1)*7,'20040101'), dateadd(dd,@w*7,'20040101') - 1

Or from the explanation something like

select dateadd(ww,@w-1,'20040101') - datepart(dw,'20040101')+1 , dateadd(ww,@w,'20040101') - datepart(dw,'20040101')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adlo
Posting Yak Master

108 Posts

Posted - 2004-11-19 : 12:03:40

Thanks for the help.
Query works great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources