StartDate,EndDate of every week in the year

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)

StartDate,EndDate of every week in the year

Author

Topic

RoniR
Starting Member

16 Posts

Posted - 2008-01-14 : 02:42:28

hey guys i need your help with something

i divided my year to 52 weeks and i want in each week to get the StartDate and EndDate
--Every Week Starts from Monday to sunday
my queries are:

1-sql = "select convert(varchar(11),dateadd(dd," & (7 * (Mid(cbWeeks.SelectedValue, 2))) - 7 & ",'01-01-" & cbYear.SelectedValue & "'),101) as Start"

were cbYear is a combo filled with values like (W1,W2,W3,W4...W52)
and the value returned from this query is saves in a variable called StartDate

2-"select convert(varchar(11),dateadd (dd,6,'" & StartDate & "'),101) as EndD "

obviously to get the EndDate

Now these queries were working fine since in 2007 the 1st Day of the year was a Monday (01/01/2007)

Now in 2008 the 1st day of the year is a tuesday and the following Select will make every week from a tuesday to a tuesday..whch is wrong

what i want is in 2008..
W1 01/01/2007 to 01/06/2007
W2 01/07/2007 to 01/13/2007
W3 01/14/2007 to 01/20/2007 and so on...

How can i produce that?
Hope i was clear..
Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-14 : 02:59:29

can you show us what is the expected result for 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

RoniR
Starting Member

16 Posts

Posted - 2008-01-14 : 03:54:58

in 2008,the 1st day of the year is Tuesday

so StartDate='01/01/2008' and EndDate='01/06/2008' (for week1)
--i added 5 days to end week 1
StartDate='01/07/2008' and EndDate='01/13/2008' (for week2)
--i added 6 days to end week 2
StartDate='01/14/2008' and EndDate='01/20/2008' (for week3) and so on

My query was working since i always add 6 days to end the week because i started the year with a monday (01/01/2007 was a monday)
(01/01/2008 is a tuesday)

and i got lost

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-14 : 04:01:19

refer to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

KH
[spoiler]Time is always against us[/spoiler]

RoniR
Starting Member

16 Posts

Posted - 2008-01-14 : 04:30:27

its not what i am looking for..
thanks for the reply :S

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 04:39:08

[code]SELECT DATEADD(DAY, 7 * Number, '20080101'),
DATEADD(DAY, 7 * Number+ 6, '20080101')
FROM master..spt_values
WHERE Type = 'p'
AND Number < 53
AND DATEADD(DAY, 7 * Number, '20080101') < '20090101'[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 04:50:37

Or see this specific topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

E 12°55'05.25"
N 56°04'39.16"

RoniR
Starting Member

16 Posts

Posted - 2008-01-14 : 05:26:41

thanks for the reply Pese..byt ur query returns the same results as the ones i had

"select convert(varchar(11),dateadd(dd,0,'01/01/2008'),101) as StartD" --01/01/2008
"select convert(varchar(11),dateadd (dd,6,'01/01/2008'),101) as EndD " --01/07/2008 if i select Week1

i want it to return
01/01/2008 and 01/06/2008 for week1 and
01/07/2008 and 01/13/2008 for week2 and
01/14/2008 and 01/20/2008 for week3 and so on

RoniR
Starting Member

16 Posts

Posted - 2008-01-14 : 05:29:49

my week starts with a Monday and ends with a Sunday

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-01-14 : 07:03:54

Adding to Pesos


declare @FirstEndDate datetime

set @FirstEndDate = (select
	case 
		when datepart(dw,'2008-01-01') = 1 then dateadd(d,7,'2008-01-01')
		when datepart(dw,'2008-01-01') = 2 then dateadd(d,6,'2008-01-01')
		when datepart(dw,'2008-01-01') = 3 then dateadd(d,5,'2008-01-01')
		when datepart(dw,'2008-01-01') = 4 then dateadd(d,4,'2008-01-01')
		when datepart(dw,'2008-01-01') = 5 then dateadd(d,3,'2008-01-01')
		when datepart(dw,'2008-01-01') = 6 then dateadd(d,2,'2008-01-01')
		when datepart(dw,'2008-01-01') = 7 then dateadd(d,1,'2008-01-01')
	end)

select '2008-01-01',
		@FirstEndDate
UNION ALL
SELECT	DATEADD(DAY, 7 * Number, @FirstEndDate+1),
	DATEADD(DAY, 7 * Number+ 6, @FirstEndDate+1)
FROM	master..spt_values
WHERE	Type = 'p'
	AND Number < 53
	AND DATEADD(DAY, 7 * Number, @FirstEndDate+1) < '20090101'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 07:18:49

What is wrong with the function provided here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 07:24:38

[code]SELECT DISTINCT CASE
WHEN START_OF_WEEK_STARTING_MON_DATE < '20080101' THEN '20080101'
ELSE START_OF_WEEK_STARTING_MON_DATE
END AS StartDate,
CASE
WHEN END_OF_WEEK_STARTING_MON_DATE > '20081231' THEN '20081231'
ELSE END_OF_WEEK_STARTING_MON_DATE
END AS EndDate,
ISO_YEAR_WEEK_NO AS YearWeekNumber
FROM F_TABLE_DATE('20080101', '20081231')
ORDER BY ISO_YEAR_WEEK_NO[/code]

E 12°55'05.25"
N 56°04'39.16"

RoniR
Starting Member

16 Posts

Posted - 2008-01-14 : 08:34:51

thanks a million..

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources