Re: Date Issue - Just shoot me again please

worked fine on my system.
I compiled:

        CREATE OR REPLACE
          FUNCTION get_PreviousWeekDay(d_date IN DATE) RETURN DATE IS
                  d_ReturnDate    DATE;
                  n_DayOfWeek     NUMBER;
          BEGIN

                  SELECT  TO_CHAR(d_date, 'D')
                  INTO    n_DayOfWeek
                  FROM    dual;

                  IF n_DayOfWeek = 2 THEN
                          d_ReturnDate := d_Date - 3;
                  ELSIF n_DayOfWeek = 1 THEN
                          d_ReturnDate := d_Date - 2;
                  ELSE
                          d_ReturnDate := d_Date - 1;
                  END IF;


                  RETURN(d_ReturnDate);

          END get_PreviousWeekDay;

Then I built a proc to actually call it:

create or replace procedure test_date
is
begin
 declare
 return_date date;
begin
 return_date:=get_PreviousWeekDay(sysdate-4);  dbms_output.put_line(to_char(return_date,'YYYYMMDD')); end;
end test_date;

Then

SQLWKS> execute test_date
Statement processed.
20000324

I think that should be the correct date.

Moore wrote:

> What in the h e l l? (again).
>
> Select to_char(sysdate, 'D') from dual; Returns
>
> TO_CHAR(SYSDATE,'D')
> ---------------------
> 6
>
> (Today is Friday, March 31).
>
> Select sysdate - 3 from dual; Returns
>
> SYSDATE-3
> ---------
> 28-MAR-00
>
> So why in the maddening hell will the following not work?
>
> CREATE OR REPLACE
> FUNCTION get_PreviousWeekDay(d_date IN DATE) RETURN DATE IS
> d_ReturnDate DATE;
> n_DayOfWeek NUMBER;
> BEGIN
>
> SELECT TO_CHAR(d_date, 'D')
> INTO n_DayOfWeek
> FROM dual;
>
> IF n_DayOfWeek = 2 THEN
> d_ReturnDate := d_Date - 3;
> ELSIF n_DayOfWeek = 1 THEN
> d_ReturnDate := d_Date - 2;
> ELSE
> d_ReturnDate := d_Date - 1;
> END IF;
>
>
> RETURN(d_ReturnDate);
>
> END get_PreviousWeekDay;
>
> I am sorry, I will state once again, working with dates in Oracle is one
> of the most royal pains that I have ever encountered in my IT life. I have
> far less trouble making API calls!
Received on Fri Mar 31 2000 - 13:34:55 CST