Re: Date Issue - Just shoot me again please

From: Michael Rothwell <marothwell_at_yahoo.com>
Date: Fri, 31 Mar 2000 13:09:24 -0800
Message-ID: <38E51404.167F95C6@yahoo.com>

OK - I'll bite.

I cut and pasted you code into my SQL session and the function created with no errors. I ran the following tests:

Today is Friday 3/31/2000

SQLWKS> select get_previousWeekDay( sysdate ) from dual GET_PREVI

---

000330
1 row selected.
SQLWKS> select get_previousWeekDay( sysdate - 4 ) from dual GET_PREVI

---

000324
1 row selected.
SQLWKS> select get_previousWeekDay( sysdate - 5 ) from dual GET_PREVI

---

000324
1 row selected.
SQLWKS> select get_previousWeekDay( sysdate - 6 ) from dual GET_PREVI

---

000324

It all seems to do exactly what you asked it to do.

Also, learn how the date masks function and you will find the working with dates in Oracle is not only easy but very powerful.

Michael

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 - 15:09:24 CST