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 ?

CodePudding user response：

You can convert yout 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

CodePudding user response：

Do this in a PL/SQL block where you can compute the date you want and compare withthat. It's much more efficient than applying a function to transform the Julian-formatted dates to Gregorian dates:

DECLARE
  nCurr_date_minus_seven  NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 7, 'YYYYDDD')) - 1900000;
BEGIN
  DELETE FROM dates
    WHERE SZUPMJ < nCurr_date_minus_seven;
END;