CODEWITHSUNDEEP
oracle-databasedatestored-proceduresplsql
Yarmiz
Yarmiz

Reputation: 149

How to compare dates in a PL/SQL procedure?

I'm a newbie in SQL programming.

I need to make a procedure that will compare dates. The only argument in the procedure is a date typed in by the user in a Java program. I need to check if that argument (date) is before a year ago. In other words, I need to compare it with SYSDATE minus a year. If that is the case, I have to "purge" all of the tables related to "schedules" (there are 2 of them).

So for example, say the user types 2013-04-13, my procedure has to compare it with SYSDATE - 1 year (in that case, it would be 2014-12-03). Since the value is less than today minus a year, the tables "MovieSchedule" and "ChannelSchedule" have to be purged. If the entered date was 2014-12-16, since it's now more than SYSDATE minus a year, the procedure has to send back an explicit error that I will be able to use in the Java program.

Now, as I said I'm completely new to procedure programming in PL/SQL, so here is what I could come up with by looking up tutorials on the internet:

CREATE OR REPLACE
PROCEDURE purge_schedule(purgeDate date) AS

DECLARE
currentDate := to_date(SYSDATE, 'YYYMMDD');

BEGIN

    -- IF purgeDate < (currentDate - 1year)
        -- delete content in "MovieSchedule" and "ChannelSchedule"
    -- ELSE
        -- return explicit error

END purge_schedule;

I don't even know if any of this is the right way to write a procedure like I want. And as you can see, my problem is how to implement my condition in the procedure, not the logic behind it. I blame my lack of practice with the language.

Please tell me if I have to be more specific of if you need more information in order to help me. Thank you for your help and have a nice day :)

Upvotes: 3

Views: 13051

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

I have illustrated a below working code which help you. Let me know if this helps.

--Compiling the stored proc
CREATE OR REPLACE PROCEDURE purge_schedule(
    purgeDate IN DATE )
AS
  currentDate DATE:=SYSDATE;
BEGIN
  IF purgeDate < ADD_MONTHS(SYSDATE,-12) THEN
    DELETE FROM EMP;
    DELETE FROM EMP_V1;
    dbms_output.put_line('Records purged successfully');
  ELSE
    RAISE_APPLICATION_ERROR(-20001,'Date provided is not in range deletion not invoked',TRUE);
  END IF;
END purge_schedule;

-- Exceuting the proc
set serveroutput on;
exec purge_schedule(to_date('12/12/2013','MM/DD/YYYY'));

--  Output
Records purged successfully

--Executing for negative scenario
set serveroutput on;
exec purge_schedule(TO_DATE('12/12/2017','MM/DD/YYYY'));

--Explicit exception raise

exec purge_schedule(TO_DATE('12/12/2017','MM/DD/YYYY'));
Error report -
ORA-20001: Date provided is not in range deletion not invoked
ORA-06512: at "AVROY.PURGE_SCHEDULE", line 11
ORA-06512: at line 1

Upvotes: 0

Shannon Severance
Shannon Severance

Reputation: 18410

Step 1, define year. Should be obvious, but is not. I have found month and year definitions vary in the wild. I've seen year defined as 52 weeks, which is never an actual year, and 365 days, which matches one year a little less than 3 of every 4 years, and occasionally 360 days! (30 days / month * 12 months)

sysdate - 365 gives a date 365 days ago.

ADD_MONTHS(sysdate, -12) will give the date 12 months ago. In the case that sysdate is February 29, the result will be February 28 of the prior year.

sysdate - interval '1' year is tempting, but interval year to month arithmetic throws errors when the "result" is a day that is not there.

select date '2012-02-28' - interval '1' year from dual;

02/28/2011

select date '2012-02-29' - interval '1' year from dual;

ORA-01839: date not valid for month specified

Upvotes: 7

Luc M
Luc M

Reputation: 17324

It should fill your needs:

IF purgeDate < SYSDATE -365 THEN
    ...
END IF;

Upvotes: 2

Related Questions