Re: Dates

You can use the LAST-DAY SQL function to get the last day of the month (eg LAST_DAY('23-MAY-2000') will give you the value ('31-MAY-2000').

If you can then write a loop which does something like the following:

Declare

    day number :=1;
    the_date date;

begin

while the_date < LAST_DAY(input_date) loop

    insert into THE_TABLE input_date;

    input_date := input_date + 1;

 end loop;

end;

This code isn't perfect but I hope you get the idea......

Regards

www.jamesbelton.co.uk

"Eleanor Sokol" <eleanor_sokol_at_bmc.com> wrote in message news:392C3526.4865FBFC_at_bmc.com...
> Hi...
>
> I'm trying to create a SQL statement which will give me all the days of
> a given month as individual rows. That is, the first row would be for
> 01May00, the 2nd row would have 02May00, through 31May00.
>
> I guess another alternative is for the SQL statement to somehow give me
> an offset which I could add to the beginning of the month (i.e.
> to_date('01May00','ddmonyy') + SQL function value.) but I would
> somehow need to be create the SQL statement such that it was able to
> "loop" through the number of days in the month.
>
> I need to include this logic in several places in a larger query so I
> don't think I could use a stored procedure or function since that
> function/procedure would have to return the same value multiple times in
> the same query.
>
> For instance, my query would use this logic in several places to
> determine the day of week - i.e. to_number(to_char(the SQL function
> date/value),'D')) and that day of week is used to retrieve data in a
> joined table.
>
> Does anyone have any ideas how I might accomplish this? I would
> appreciate an email to eleanor_sokol_at_bmc.com.
>
> Thanks
> Elly Sokol
>
Received on Wed May 24 2000 - 00:00:00 CDT