Oracle SQL to delete the records except last 7 days/1 week

I am using Oracle SQL DB for ERP JD Edwards. Dates are stored in Julian Format for this ERP.

We usually use this code to convert date from Julian to normal format. decode(szupmj,0,’ ‘,to_char(to_date(1900000 + olupmj,’YYYYDDD’),’MM/DD/YYYY’))

However we have a requirement where in, all data needs to be deleted except last 7 days. Date column – 7

Please can someone help ?

SZEDUS	SZEDBT	SZUPMJ
ILPPO	74442	106108
ILPPO	74442	106108
ILPPO	77292	106109

Answer

You can convert your juliandate to a regular date and compare it to sysdate

Please test this first on a test database or use the dbfiddole to add rows with dates that are only a few days old

CREATE TABLE dates (
  "SZEDUS" VARCHAR(5),
  "SZEDBT" INTEGER,
  "SZUPMJ" INTEGER
);

INSERT INTO dates
  ("SZEDUS", "SZEDBT", "SZUPMJ")
VALUES
  ('ILPPO', '74442', '106108');

INSERT INTO dates
  ("SZEDUS", "SZEDBT", "SZUPMJ")
VALUES

  ('ILPPO', '74442', '106108');

INSERT INTO dates
  ("SZEDUS", "SZEDBT", "SZUPMJ")
VALUES
  ('ILPPO', '77292', '106109');

SELECT * FROM dates

SZEDUS | SZEDBT | SZUPMJ
:----- | -----: | -----:
ILPPO  |  74442 | 106108
ILPPO  |  74442 | 106108
ILPPO  |  77292 | 106109

DELETE FROM dates WHERE to_date(1900000 + SZUPMJ,'YYYYDDD') < trunc(sysdate) - 7 

3 rows affected

SELECT * FROM dates

SZEDUS | SZEDBT | SZUPMJ
:----- | -----: | -----:

db<>fiddle here