Re: Dates

Will pl/sql using a cursor help - then you can select all the rows for a month and then loop thru them one at a time....

declare

cursor date_cursor is

    select mydate from mytable;

begin

  for date_record in date_cursor loop
    do what you want to do with the date   end loop;
end;

cheers

Sue E
Eleanor Sokol wrote in message <392C424A.3D9A9AD0_at_bmc.com>...
>Thanks... but my problem becomes how do I access the day by day entries in
>THE_TABLE within my SQL query which needs to know the day of the week?
 i.e.
>out of all the rows in THE_TABLE how do I pick information from just 1 row
 for
>each row each time and multiple times within the query?
>
>If part of my query says:
>select (what do I put here to get each day of the month?)
>...
>and joinedtable.weekday =
> to_number(to_char(what do I put here to get each day of the
>month?),'D'))
>....
>
>Elly
>
>James Belton wrote:
>
>> 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 Thu May 25 2000 - 00:00:00 CDT