Re: Sequence Of Dates ( Posting 2nd Time)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> How do i generate a sequence of consecutive dates between 2 dates in a
> single query?
>
> I don't want to use any pgsql or any othere procedural languag. The result
> should be got in a single sql statement. Like in oracle I can do it as
> follows by using one of my tables...

Tough requirements! Asssume you have a sequence 's':

CREATE SEQUENCE s;

Assume you also have a table with enough rows to comfortably cover the
number of dates you expect ot be returned. In this case, I'll use
the system table pg_proc, which should have more than enough rows
for this example.

Given those two requirements, you could do this:

SELECT TO_DATE(
(SELECT setval('s',TO_CHAR('01-01-2003'::timestamp,'J')::integer)),'J')
UNION
SELECT TO_DATE(nextval('s'),'J') FROM pg_proc
WHERE currval('s') < TO_CHAR('01-04-2003'::timestamp,'J')::integer;

To return the dates in the format you want (Month-Day-Year):

SELECT TO_CHAR(TO_DATE(
(SELECT setval('s', TO_CHAR('01-01-2003'::timestamp, 'J')::integer)), 'J'),
'MM-DD-YYYY')
UNION
SELECT TO_CHAR(TO_DATE(nextval('s'), 'J'),'MM-DD-YYYY') FROM pg_proc
WHERE currval('s') < TO_CHAR('01-04-2003'::timestamp,'J')::integer;

Far better would be to use whatever language your application is written in: most
have good interation and date-manipulation features which would get the info
much more easily.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200303061000

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+Z2Q6vJuQZxSWSsgRAmZ9AKDeWJS7NS+grseOqTK9hyBfSxn+iACg1KuN
JuHJoqO773GA0+4zSENO+KU=
=Cosf
-----END PGP SIGNATURE-----