Thread: seleting all dates between two dates
I am interested in a query where I can select all dates between two dates. I figure I can build a table of all valid dates with a resonable range and then select from that table, but I would like to use the power of sql to get the work done without building a date table. Any ideas? For example: I want all dates between 05-29-2001 and 06-02-2001 The result set would be: 05-30-2001 05-31-2001 06-01-2001 Thanks for the help. -Jeff
Jeff, > I am interested in a query where I can select all dates between two > dates. I > figure I can build a table of all valid dates with a resonable range > and > then select from that table, but I would like to use the power of sql > to get > the work done without building a date table. Any ideas? > > For example: > I want all dates between 05-29-2001 and 06-02-2001 > The result set would be: > 05-30-2001 > 05-31-2001 > 06-01-2001 If you browse last week's postings, you will find a thread called "SQL Date Challenge" with that very issue. The answer, in short, is that you can't do it in SQL. Your choices are to build the reference table, or to use and external procedural language (such as Perl). -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Fri, 1 Jun 2001 15:09:33 -0400 "Jeff Barrett" <jbarrett@familynetwork.com> wrote: > I am interested in a query where I can select all dates between two dates. I > figure I can build a table of all valid dates with a resonable range and > then select from that table, but I would like to use the power of sql to get > the work done without building a date table. Any ideas? > > For example: > I want all dates between 05-29-2001 and 06-02-2001 > The result set would be: > 05-30-2001 > 05-31-2001 > 06-01-2001 You got it almost right already! Use the "between" operator; *sometimes* SQL *is* intuitive: E.g. in my database: select run_name from runs where comp_date between '2001-may-10' and '2001-jun-5'; run_name | comp_date ------------------+------------------------spcsil-103-RP | 2001-06-04 21:09:35-04airtest_0120 | 2001-05-21 20:10:50-04spcsil-101-RP | 2001-05-23 17:44:11-04... -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK]
Jeff Barrett schrieb: > I am interested in a query where I can select all dates between two dates. I > figure I can build a table of all valid dates with a resonable range and > then select from that table, but I would like to use the power of sql to get > the work done without building a date table. Any ideas? > > For example: > I want all dates between 05-29-2001 and 06-02-2001 > The result set would be: > 05-30-2001 > 05-31-2001 > 06-01-2001 > > Thanks for the help. > -Jeff In my opinion there is no reasonable way to solve your problem by using SQL only Why don't you use a temporary table created by a simple PL/pgSQL function? Hans