Getting date range for the week.

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

Transact-SQL (2005)

Getting date range for the week.

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-21 : 04:49:25

Hi guys,

Is there a way to get the daterange for a week.I mean I get the week number for the year using datepart(wk,getdate()).But I need to the get the startdate & enddate of the week.
Thanks for any help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-21 : 04:57:03

This may give some ideas

select min(dates) as min_date,max(dates) as max_date,datepart(wk,dates) as week_no from
(
	select dateadd(day,number,dateadd(year,datediff(year,0,getdate()),0)) as dates from
	master..spt_values where type='p' and number<=365
) as t
group by datepart(wk,dates)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 05:32:26

What is a week for you?
How are week numbers changed over new years eve?

Do you use ISO rules?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 05:37:12

[code]DECLARE @WeekNumber int,@YearDate datetime,@yearDay int

SET @WeekNumber=52
SELECT @YearDate=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0),@YearDay=DATEPART(dw,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
SELECT DATEADD(dd,-1*(@yearDay-1),DATEADD(wk,@WeekNumber-1,@YearDate)),DATEADD(dd,-1*(@yearDay-1)+6,DATEADD(wk,@WeekNumber-1,@YearDate))[/code]

you can pass any value for @weeknumber and will get start & end dates accordingly for current year.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-21 : 05:53:32

I wanted to get the startdate & enddate of the week for a date that lies in that particular week.I thought that the approach by going by weekno of the year was better & then by the weekno, I can get the daterange of that week.But I found a much better way.My first day of the week is monday hence datefirst 1.Here is the sql.

Set datefirst 1
select (DATEADD(wk, DATEDIFF(wk,7, getdate()), 7))as startdate ,
(DATEADD(dd,7,DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6)))as enddate

Thanks for the help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 06:00:25

No need for SET DATEFIRST because that triggers a recompilation

SELECT	DATEADD(DAY, DATEDIFF(DAY, '17530101', GETDATE()) / 7 * 7, '17530101'),
	DATEADD(DAY, DATEDIFF(DAY, '17530101', GETDATE()) / 7 * 7, '17530107')

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-21 : 06:09:35

quote:
Originally posted by Peso

No need for SET DATEFIRST because that triggers a recompilation
SELECT DATEADD(DAY, DATEDIFF(DAY, '17530101', GETDATE()) / 7 * 7, '17530101'),
DATEADD(DAY, DATEDIFF(DAY, '17530101', GETDATE()) / 7 * 7, '17530107')

E 12°55'05.63"
N 56°04'39.26"

Thanks for the info.But any reason why it happens.In books online the following is stated.

The setting of SET DATEFIRST is set at execute or run time and not at parse time.
But no mention of recompilation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources