Determining dates For Saturday for given mmyyyy ...

[request]

if for a given month/year, I need to determine the dates for all the Saturdays that month/year and store them in an array, how will I do it?

Please help???

 

[SantaMufasa]

Below is a stored procedure that does what you request. I added the "dbms_output.put_line" to display results and confirm proof of concept. Cheers

create or replace procedure Saturdays (MonYear in varchar2) is
-- "MonYear" must be in the format "mm/yyyy"
start_date date;
new_base_date date;
which_week binary_integer;
type date_stencil is table of date index by binary_integer;
saturday_table date_stencil;
begin
start_date := to_date('01/'||MonYear,'dd/mm/yyyy');
which_Week := 0;
new_base_date := start_date;
while to_char(start_date,'MON') =
to_char(next_day(new_base_date,'SATURDAY'),'MON') loop
which_week := which_week+1;
saturday_table(which_week) := next_day(new_base_date,'SATURDAY');
new_base_date := saturday_table(which_week)+1;
dbms_output.put_line (saturday_table(which_week));
end loop;
end;
/
SQL> exec saturdays('01/2003')
04-JAN-03
11-JAN-03
18-JAN-03
25-JAN-03

 

[lewisp]

This assumes your database has Saturday = day 7 and that ALL_OBJECTS is visible and contains >= 31 rows. Substitute &1 for your chosen month/year in 'Mon-YYYY' format:

[tt]SELECT *
FROM (
SELECT To_Date(Lpad(To_Char(rownum),2,'0') ||
'-' || '&1','DD-Mon-YYYY') dat
FROM all_objects
WHERE rownum <= To_Number(To_Char(Last_Day(To_Date('01-' ||
'&1','DD-Mon-YYYY')),'DD')) )
WHERE To_Char(dat,'D') = '7';
[/tt]

 

[request]

lewisp:

Thanks for your great response.

Is there a way to store all the weekend dates in an array so that i can go thru a loop and read each value of weekend date.

Please let me know.

thanks a ton.

 

[carp]

How persistent does your array need to be?
If it is for the duration of the procedure, see SantaMufasa's code for the technique (it's storing the data in an array called saturday_table). If it needs to be permanent, load it into a table.

 

[request]

carp:

Since I am going to use the query in a stored procedure, I have used PL/SQL table for an array declaration.

Now, I have to determine for a given weekend_date, how many days actually belong to a given month/year. How can I do it?

e.g. if the input month/year is Jan 2003, then for the weekend_date 01/04/2003, the number of days that actually belong to Jan 2003 is 3.

Please help.

Thanx.

 

[carp]

Your last post makes no sense to me.
If you want the number of days in a month/year andthe weekend_date is 01/04/2003, then the days that belong to the given month/year would be 31!
There are 31 days in January, 2003.
There are four Saturdays in January, 2003.
Where did &quot;3&quot; come from?

 

[SantaMufasa]

Request,

To clarify your previous post (which also puzzled me), do you mean that as of 04Jan03 (the first Saturday of Jan 03), that there have been 3 days SO FAR in January? Then, for the following Saturday, 11Jan03, that there are 10 days SO FAR in January prior to that Saturday? If I'm off-logic here, please correct me, but we do need a few more specs from you.

Dave

 

[request]

Please let me state an example to clarify my question.

e.g. in jan 2003, there are 5 weeks.

However, for the first week, the number of WEEKDAYS that acutally belong to Jan 2003 is 3.
For the second, third, fourth weeks, the number of WEEKDAYS that actually belong to Jan 2003 is 15 (5 days per week Mon-Fri).
However, for the last week in Jan 2003, the number of WEEKDAYS that actually belong to Jan 2003 is 5.

I am using the logic that SantaMufasa had suggested to calculate Saturday dates. Now, how can I incorporate the above logic into SantaMufasa's logic so that I can also have the number of WEEKDAYS that actually belong to a given month/year in each week.

thanks a ton.

 

[Turkbear]

Hi,
In general, loop through the month and increment a counter each time the to_char(date,'DY') function returns a weekday
( As MON,TUE,WED,THU,FRI).

 

[carp]

CREATE OR REPLACE
FUNCTION weekdays(p_first_date IN DATE, p_last_date IN DATE)
RETURN NUMBER IS
l_first_date DATE;
l_last_date DATE;
l_weekdays NUMBER := 0;
BEGIN
-- CHECK FOR STUPID USER TRICK #47
IF (p_first_date > p_last_date) THEN
l_first_date := p_last_date;
l_last_date := p_first_date;
ELSE
l_first_date := p_first_date;
l_last_date := p_first_date;
END IF;
WHILE (l_first_date <= l_last_date) LOOP
IF (to_char(l_first_date,'fmDAY') NOT IN ('SATURDAY','SUNDAY')) THEN
l_weekdays := l_weekdays + 1;
END IF;
l_first_date := l_first_date + 1;
END LOOP;
RETURN l_weekdays;
END;

SQL> select weekdays('9-AUG-03','1-AUG-03') from dual;

WEEKDAYS('9-AUG-03','1-AUG-03')
-------------------------------
6