Обсуждение: 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