How to get the list of dates between two dates usind sql query?

Question

How to get the list of dates between two dates usind sql query?

shivanee

SSC Veteran

Points: 233
More actions
June 26, 2008 at 6:22 am

#103520

Hello All,
I have one table name "abc" and having 2 fields as "Start_Date" and "End_Date" which is having the values like '06/26/2008' and '06/30/2008'.
Now I want the list of dates including these two dates by using the sql query.
ForEg:
06/26/2008
06/27/2008
06/28/2008......upto 06/30/2008.
Could anybody please tell me how should I achieve this?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Search for "Tally Table" on this site and you will find some articles by Jeff about how to do this. He's a pretty smart guy and his articles are good.

If my memory serves me, there is at least one article with the T-SQL to do exactly what you want.

schuppe SSC Enthusiast Points: 113 More actions · Answer 2

create table testtable (startdate datetime, enddate datetime)

insert into testtable values(getdate(), dateadd(day, 5, getdate()));

with test (startdate, enddate) as

(select startdate, enddate from testtable

union all

select dateadd(day, 1, test.startdate), test.enddate from testtable cross join test where test.startdate < test.enddate)

select * from test

drop table testtable

GSquared SSC Guru Points: 260824 More actions · Answer 3

You can use a Numbers table for this. Or a recursive CTE. I use a calendar table for it. It's generally faster, and it gives me more options, like a really easy way to filter out weekends and holidays.

- 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

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

Michael Earl (6/26/2008)
Search for "Tally Table" on this site and you will find some articles by Jeff about how to do this. He's a pretty smart guy and his articles are good.

Michael... thanks for the awesome referral... :blush:

Here's the article you're referring to... the "date generator" code you're referring to is under the "Dozens of Other Uses" heading near the end of the article...

http://www.sqlservercentral.com/articles/TSQL/62867/

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

schuppe (6/26/2008)
create table testtable (startdate datetime, enddate datetime)
insert into testtable values(getdate(), dateadd(day, 5, getdate()));
with test (startdate, enddate) as
(select startdate, enddate from testtable
union all
select dateadd(day, 1, test.startdate), test.enddate from testtable cross join test where test.startdate < test.enddate)
select * from test
drop table testtable

Ummm.... be a bit careful with that... it produces times on the dates. Also, it uses recurrsion... recurrsion is a form of "hidden RBAR" than can actually be slower than a While loop.

--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)