Find Start-of-Week Given only a Week Value

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

General SQL Server Forums

New to SQL Server Programming

Find Start-of-Week Given only a Week Value

Author

Topic

Icculus
Starting Member

2 Posts

Posted - 2010-06-11 : 12:00:11

I'd like to write a function that given a year and a week-of-the-year (1-52), would be able to provide the first day of that week.

For example: if given 2007 and week# 6....i'd like it to return something like 02-08-2007

Any ideas?
Thanks.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-11 : 12:26:24

Have a look at this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

The function will have what you want in it somewhere, if not, i'm sure there are posts on here that already answer the question, just do a search on the forum for week number or start of week.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-11 : 12:33:53

You really need to define your organizations definition of week of year first for us to be able to provide specific help.

The definition varies widely from one organizaation to the next. Some use the ISO standard, but there are many others in use.

CODO ERGO SUM

Icculus
Starting Member

2 Posts

Posted - 2010-06-11 : 13:15:13

By week-of-year, i simply mean whatever week# the datepart(ww,getdate()) function would return.

quote:
Originally posted by Michael Valentine Jones

You really need to define your organizations definition of week of year first for us to be able to provide specific help.

The definition varies widely from one organizaation to the next. Some use the ISO standard, but there are many others in use.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-11 : 14:34:22

This will do what you are asking, but you should be aware that it will return a different value, depending on the setting of DATEFIRST.


select
	Week_Start = min(a.DT)
from
	(
	select DT = dateadd(dd,datediff(dd,0,getdate()),0)	union all
	select DT = dateadd(dd,datediff(dd,0,getdate())-1,0)	union all
	select DT = dateadd(dd,datediff(dd,0,getdate())-2,0)	union all
	select DT = dateadd(dd,datediff(dd,0,getdate())-3,0)	union all
	select DT = dateadd(dd,datediff(dd,0,getdate())-4,0)	union all
	select DT = dateadd(dd,datediff(dd,0,getdate())-5,0)	union all
	select DT = dateadd(dd,datediff(dd,0,getdate())-6,0)
	) a
where
	datepart(ww,getdate()) = datepart(ww,a.DT)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources