Thread: 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
resultshould 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
recordsper 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