| Previous Day? [message #1658] |
Mon, 20 May 2002 05:53  |
prj45
Messages: 1
Registered: May 2002
|
Junior Member |
|
|
|
How can I find out the date of the previous day in Oracle PLSQL, I note there is a NEXT_DAY function, but no PREVIOUS_DAY function...?
|
|
|
|
|
|
| Re: Previous Day? [message #1732 is a reply to message #1658] |
Fri, 24 May 2002 08:03  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Assuming that you are looking for a function that behaves like the NEXT_DAY function, that is accepts a DATE and a DAY of the WEEK as input and returns the first DATE that falls on that DAY prior to the DATE you have entered, then you could write your own, or use mine (it's a bit sloppy in that it loops the full 7 times even when it finds the correct date on the first iteration of the loop, but it works).
CREATE OR REPLACE FUNCTION PREVIOUS_DAY(date_in DATE, day_in VARCHAR2)
RETURN DATE AS
VAL_OUT DATE;
THIS_DATE DATE;
V_TARGET VARCHAR2(10);
BEGIN
THIS_DATE := date_in;
V_TARGET := TRIM(UPPER(day_in));
FOR i IN 1..7 LOOP
THIS_DATE := THIS_DATE - 1;
IF TRIM(TO_CHAR(THIS_DATE,'DAY')) = V_TARGET THEN
VAL_OUT := THIS_DATE;
END IF;
END LOOP;
RETURN VAL_OUT;
END;
/
-- test the function
select previous_day(sysdate,'MONDAY') from dual;
select previous_day(sysdate,'TUESDAY') from dual;
select previous_day(sysdate,'WEDNESDAY') from dual;
select previous_day(sysdate,'THURSDAY') from dual;
select previous_day(sysdate,'FRIDAY') from dual;
select previous_day(sysdate,'SATURDAY') from dual;
select previous_day(sysdate,'SUNDAY') from dual;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
