Re: 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!