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

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.

sql 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?

This is version #2. Still no luck.

sql 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/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;

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?

Quote:

---

Originally Posted by 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?

---

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

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?

vb Code:

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

vb Code:

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

vb Code:

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

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

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.

Code:

---

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.

Quote:

---

Originally Posted by 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?

vb Code:

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

vb Code:

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

vb Code:

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.

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