How to get Saturday b/w two dates

Question

How to get Saturday b/w two dates

siv123

SSC Rookie

Points: 39
More actions
June 12, 2009 at 9:03 am

#206254

Hi,
I am seeking for getting list of saturdays between two dates (ex: 07/13/2008 to 08/13/2008), after getting how to store them in variable
Please hlep, if you have any function or query ...
R/Siva

Viewing 7 posts - 1 through 7 (of 7 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

The best way is to build and use a calendar table. It's just a table with a list of dates, and pertinent facts about them, like day of week, holiday data, etc.

Second best is, if you have a Numbers/Tally table, you can use dateadd(day) with that to get a list of all dates between the two, and then query that for weekday.

Which one are you more interested in?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

siv123 SSC Rookie Points: 39 More actions · Answer 2

I feel that calendar table will help, but i have already date columns and values in the table, hence do i need to go for calendar table or is there any easiest way to get the details quickly

LutzM SSC Guru Points: 107049 More actions · Answer 3

Since you already have date columns in your table, the following might help

select from where datename(dw,dateCol)='Saturday'

and datecol >=@start

and datecol <=@end

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

Just in case...

[font="Courier New"] SELECT DATEADD(wk,DATEDIFF(wk,-2,'07/13/2008')+t.Number,-2)

FROM Master.dbo.spt_Values t

WHERE t.Type = 'P'

AND t.Number < DATEDIFF(wk,'07/13/2008','08/13/2008')+1

AND DATEADD(wk,DATEDIFF(wk,-2,'07/13/2008')+t.Number,-2) <= '08/13/2008'[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

siv123 SSC Rookie Points: 39 More actions · Answer 5

Hi,

Thank you for the information on datename, which worked out, still i need the following info.

I would like to arrive the sum of set values from one column where dates exist as 'Saturday' ...

please share if you hv any info...

cheers .... :),

siv

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

Lutz's example is the start. You just need to add the SUM code which should be no biggee.

If you need a more complete answer, we need to see some readily consumable test data. Please see the first link in my signature below for the best way to do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)