Обсуждение: Query to find list of dates between two dates
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
Thanks,
Aravind.
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
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