[RESOLVED] [Oracle 9i] PL/SQL Date Arithmetic

[abhijit]

I have this block which gives me all the SUNDAYS between a given date range. This prints all the sundays, but there's a bug in this code.

/*
                  Anonymous block to get all sundays between a given date range
*/
DECLARE
FROM_DT DATE:= TO_DATE('01/01/2009','mm/dd/yyyy');
TO_DT DATE:= TO_DATE('01/31/2009','mm/dd/yyyy');
X VARCHAR2(30);
BEGIN
      WHILE NOT FROM_DT > TO_DT
      LOOP
        SELECT NEXT_DAY(FROM_DT,'SUN') INTO X from dual;
         --I want to print X only if it is a SUNDAY and just once.
                DBMS_OUTPUT.PUT_LINE(X);
        FROM_DT := FROM_DT + 1;
      END LOOP;  
END;

In the above code, I want to print each date, just once.

Any idea?

[abhijit]

This is version #2. Still no luck.

/*
              Anonymous block to get all sundays between a given date range
*/
DECLARE
  from_dt DATE := to_date('01/01/2009', 'mm/dd/yyyy');
  to_dt   DATE := to_date('01/30/2009', 'mm/dd/yyyy');
  x       VARCHAR2(30);
BEGIN
 
  WHILE NOT FROM_DT > TO_DT
  LOOP
    IF to_date(x, 'dd-mon-yyyy') >= to_dt THEN
      dbms_output.put_line('hello');
      EXIT;
    ELSE
      --dbms_output.put_line('FROM DATE ==>' || from_dt);
      x := next_day(from_dt, 'SUN');
      IF to_date(x, 'DD-MON-YYYY') > to_dt THEN
        dbms_output.put_line('Hello');
      ELSE
        dbms_output.put_line('Sundays -->' || x);
      END IF;
    END IF;
    from_dt := from_dt + 7;
  END LOOP;
END;

[BackWoodsCoder]

so what's the output like? You say you want to print the dates just once, I'm assuming you're repeating sunday's somewhere in your result set?

[abhijit]

so what's the output like? You say you want to print the dates just once, I'm assuming you're repeating sunday's somewhere in your result set?

Here's my output.

Output:

Sundays -->04-JAN-09
Sundays -->11-JAN-09
Sundays -->18-JAN-09
Sundays -->25-JAN-09
Sundays -->01-FEB-09 <-- Should not print 01-FEB-2009

[BackWoodsCoder]

here's a stupid question, why do you format your dates one way in the beginning but use a different format later on in your code?

to_dt   DATE := to_date('01/30/2009', 'mm/dd/yyyy');

IF to_date(x, 'dd-mon-yyyy') >= to_dt THEN

IF to_date(x, 'DD-MON-YYYY') > to_dt THEN

[GaryMazzone]

If x<= to_dt then
dbms_output.put_line('Sundays -->' || x);
End If;

[leinad31]

Inclusion of extraneous date due to this line x := next_day(from_dt, 'SUN'); which made you jump to next week without validation (validation was performed in WHILE condition).

Use this instead... more reusable.

CREATE OR REPLACE TYPE DATE_COLLTYP
   AS TABLE OF DATE;
/

GRANT EXECUTE ON DATE_COLLTYP TO PUBLIC; -- grant to public since its just an "array" data type 
/

CREATE OR REPLACE FUNCTION list_days (start_dt DATE, end_dt DATE, weekday CHAR)
   RETURN date_colltyp
AS 
   ret_list   date_colltyp := date_colltyp(); -- initialize collection
   v_date     DATE;   
BEGIN
   BEGIN
      v_date := NEXT_DAY(start_dt, weekday); -- first sunday after start date
      
      WHILE v_date <= end_dt 
      LOOP
         ret_list.EXTEND;
         ret_list(ret_list.COUNT) := v_date;
         v_date := v_date + 7;
      END LOOP;
      
   EXCEPTION
   WHEN OTHERS THEN 
      NULL; -- or log error via autonomous transaction, or raise_application_error
   END;
   
   RETURN ret_list;
END;
/

/* sample usage via SELECT */
SELECT TO_CHAR(COLUMN_VALUE, 'YYYY-MM-DD') AS sundays 
  FROM TABLE(list_days(SYSDATE - 100, SYSDATE, 'SUN')); -- use table function to convert collection to result set

SELECT *
  FROM TABLE(list_days(TRUNC(SYSDATE - 100), TRUNC(SYSDATE), 'mondays'));

And I suggest you also create collection types for NUMBER, VARCHAR2(4000), and VARCHAR2(32767). varchar2_32k_colltyp can be used for functions that wrap file I/O using UTL_FILE. varchar2_4k can be used for functions that wrap java.io.File, e.g. directory listing via Java stored procedure.

[abhijit]

here's a stupid question, why do you format your dates one way in the beginning but use a different format later on in your code?

to_dt   DATE := to_date('01/30/2009', 'mm/dd/yyyy');

IF to_date(x, 'dd-mon-yyyy') >= to_dt THEN

IF to_date(x, 'DD-MON-YYYY') > to_dt THEN

X is a varchar and the Next_Day function returns the date in that particular format. So the conversion.

[BackWoodsCoder]

Ah, I see, couldn't wrap my head around that one.