Return date for every sunday between years

Question

Post reply

Return date for every sunday between years

catriona_brown

SSC-Addicted

Points: 435
More actions
May 23, 2008 at 3:08 am

#71418

Hi,
I need to return the date for every Sunday between years
i.e. Every Sunday from 06/01/2008 and 03/01/2010
So it would return :
06/01/2008
13/01/2008
20/01/2008
27/01/2008
03/02/2008
10/02/2008
17/02/2008
----
---
03/01/2010
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

dva2007 SSCertifiable Points: 7707 More actions · Answer 1

hi,

this can give you last sunday. You need to manipulate date to get it every sunday.

select dateadd(day,1-datepart(dw, getdate()), getdate());

RyanRandall SSChampion Points: 13623 More actions · Answer 2

select Date from (select dateadd(d, N-1, '20080106') as Date from dbo.Tally) a

where datename(weekday, Date) = 'Sunday' and Date <= '20100103'

See this article for info on how to create the Tally table: http://www.sqlservercentral.com/articles/TSQL/62867/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

raja_saminathan SSCommitted Points: 1607 More actions · Answer 3

Hi,

Look at this query below

Declare @Temp Table (Date datetime)

dECLARE @DD datetime,@dd1 datetime

set @dd ='03/01/2010'

set @DD1 = '06/01/2008'

while (@dd1 <@DD)

begin

set @DD1 = (select dateadd(day,1-datepart(dw,'06/01/2008'),@dd1))

insert into @temp

select @DD1

set @dd1 =dateadd(day,7,@dd1)

end

select * from @temp

rajesh

karthik M One Orange Chip Points: 29139 More actions · Answer 4

karthik M

One Orange Chip

Points: 29139

May 23, 2008 at 5:43 am

#819597

Concur...

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Raja,

if you have free time just go through the link which is referred by Ryan, after that you never think RBAR logic (While or Cursor). Really it will change your thought.

karthik

catriona_brown SSC-Addicted Points: 435 More actions · Answer 6

catriona_brown

SSC-Addicted

Points: 435

May 23, 2008 at 6:00 am

#819604

That's great, thanks

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Why count every date from Tally table when every 7th will do?

set statistics io on

-- Ryan

select Date from (select dateadd(d, Number-1, '20080106') as Date from dbo.Tally) a

where datename(weekday, Date) = 'Sunday' and Date <= '20100103'

-- Peso

SELECTDATEADD(DAY, 7 * Number, '20080106') AS aSunDay

FROMdbo.Tally

WHERENumber <= DATEDIFF(WEEK, '20080106', '20100103')

set statistics io off

Results are

-- Ryan

Table 'Tally'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Peso

Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

RyanRandall SSChampion Points: 13623 More actions · Answer 8

Peso (5/23/2008)
Why count every date from Tally table when every 7th will do?
set statistics io on
-- Ryan
select Date from (select dateadd(d, Number-1, '20080106') as Date from dbo.Tally) a
where datename(weekday, Date) = 'Sunday' and Date <= '20100103'
-- Peso
SELECTDATEADD(DAY, 7 * Number, '20080106') AS aSunDay
FROMdbo.Tally
WHERENumber <= DATEDIFF(WEEK, '20080106', '20100103')
set statistics io off
Results are
-- Ryan
Table 'Tally'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Peso
Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What's with the attacking approach?

I guess I thought that the assumption that the start date was a Sunday was less important than increasing the time from 0.006 seconds to 0.001 seconds :w00t:

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

No Ryan, it wasn't an attack against you. You know that. We have known each other a long time now. You have improved my queries and I have improved your queries equally long.

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

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

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=101117

You have tought me how to improve my correlated subquery techniques.

I wanted to show another approach to accomplish same task, with less resources involved.

And it isn't just this topic. It is for all topics where you simply could write more efficient queries. This specific topic didn't gain that much.

When OP get to next problem he might remember "Aha, that is a another way to do it".

If you were offended, I apologize.

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