Query to find list of dates between two dates

On 5 Feb 2010, at 11:06, aravind chandu wrote:

Hello guys,

can you please help me with the following query

I need a query that displays all the dates in between two dates say i give two dates 12/1/2009 and 12/31/2009 The result should be like this

12/1/2009
12/2/2009
12/3/2009

.
.
.
.
.
12/31/2009

Note : Assume that there is no table

You can do that using generate_series(), like this:

select '2009-12-01'::date + d.date
from generate_series(0, 99) as d(date)
where '2009-12-01'::date + d.date BETWEEN '2009-12-01'::date and '2009-12-31'::date;

The query assumes a 100 days (hence 0-99) will be enough and not overly many. If you need more flexibility or better performance (especially when you need larger ranges) you probably should fill a table with dates. That's only 365 records per year, not particularly expensive. You can use generate_series() to do that as well.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b6c070b10441449311484!

You could use a query like this one here:

and table.BEGIN_DATE <= pREFERENCE_END_DATE
and (table.END_DATE >= pREFERENCE_BEGIN_DATE or table.END_DATE is null)

Best Regards

On Fri, 5 Feb 2010 02:06:12 -0800 (PST)
aravind chandu <avin_friends@yahoo.com> wrote:

Hello guys,

can you please help me with the following query

I need a query that displays all the dates in between two dates
say i give two dates 12/1/2009 and 12/31/2009 The result should be
like this

select
date '2008-05-01' + i
from generate_series(0,
(date '2009-12-10' - date '2008-05-01')) s(i);

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

aravind chandu wrote:

can you please help me with the following query

I need a query that displays all the dates in between two
dates say i give two dates 12/1/2009 and 12/31/2009 The
result should be like this

12/1/2009
12/2/2009
12/3/2009

.
.
.
.
.
12/31/2009

Note : Assume that there is no table

SELECT CAST(d AS date)
FROM generate_series(
CAST('2009-12-01' AS timestamp with time zone),
'2009-12-31',
'1 day') AS g(d);

Yours,
Laurenz Albe