Re: Sql - list all dates in month, year etc

From: Tomm Carr <tommcatt_at_geocities.com>
Date: 1997/07/21
Message-ID: <33D37E2E.2F9@geocities.com>#1/1

Steve Corbett wrote:
>
> is there anything like 'select sysdate from dual' that will list all the
> dates in a particular month or year etc? without having to have a table
> of all possible dates?
> i want to join this list to another table to end up with something like:
> Date Count
> 01-FEB-97 3
> 02-FEB-97 0
> 03-FEB-97 2
> 04-FEB-97 0
> ...
> 28-FEB-97 9
>
> that is, i want the date listed even if the count is zero.
>

If this is something you will be using a lot, why not just create a table that has all the dates of the year in it? The script to create and populate such a table would be something like:

  create table days_of_year ( this_day date primary key)   ...
  declare

     running_date  date  := to_date( '1-jan-1997' );
     end_date      date  := to_date( '31-dec-1997' );
  begin
     while running_date <= end_date loop
        insert into days_of_year set this_day = running_date;
        running_date := running_date + 1;
     end loop;

  end;

Then you can use the dates in joins or what-have-you. As a byproduct,  the simple addition of a number field of sequential values ( 1, 2, 3,...365) would allow you to create a simple julian date converter.

*Perhaps* a list of dates of a certain year can be generated in a SQL statement but, right off the top of my head, I would guess that it is not possible. But even if it is, try to avoid the look-what-I-can-do-in-a-single-select-statement syndrone. It is usually counter-productive. (Thoughts, anyone?)

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"

Received on Mon Jul 21 1997 - 00:00:00 CDT