Oracle SQL Developer get table rows older than n months-CodePudding

In Oracle SQL Developer, I have a table called t1 who have two columns col1 defined as NUMBER(19,0) and col2 defined as TIMESTAMP(3). I have these rows

col1           col2
1     03/01/22 12:00:00,000000000
2     03/01/22 13:00:00,000000000
3     26/11/21 10:27:11,750000000
4     26/11/21 10:27:59,606000000
5     16/12/21 11:47:04,105000000
6     16/12/21 12:29:27,101000000

My sysdate looks like this:

select sysdate from dual;

    SYSDATE
    03/03/22

I want to create a stored procedure (SP) which will delete rows older than 2 months and displayed message n rows are deleted

But when i execute this statement

select * from t1 where to_date(TRUNC(col2), 'DD/MM/YY') < add_months(sysdate, -2);

I don't get the first 2 rows of my t1 table. I get more than 2 rows

1     03/01/22 12:00:00,000000000
2     03/01/22 13:00:00,000000000

How can i get these rows and deleted it please ?

CodePudding user response：

In Oracle, a DATE data type is a binary data type consisting of 7 bytes (century, year-of-century, month, day, hour, minute and second). It ALWAYS has all of those components and it is NEVER stored with a particular formatting (such as DD/MM/RR).

Your client application (i.e. SQL Developer) may choose to DISPLAY the binary DATE value in a human readable manner by formatting it as DD/MM/RR but that is a function of the client application you are using and not the database.

When you show the entire value:

SELECT TO_CHAR(ADD_MONTHS(sysdate, -2), 'YYYY-MM-DD HH24:MI:SS') AS dt FROM DUAL;

Then it outputs (depending on time zone):

DT

2022-01-03 10:11:28

DT
2022-01-03 10:11:28

If you compare that to your values then you can see that 2022-01-03 12:00:00 is not "more than 2 months ago" so it will not be matched.

What you appear to want is not "more than 2 months ago" but "equal to or more than 2 months, ignoring the time component, ago"; which you can get using:

SELECT *
FROM   t1
WHERE  col2 < add_months(TRUNC(sysdate), -2)   INTERVAL '1' DAY;

SELECT *
FROM   t1
WHERE  TRUNC(col2) <= add_months(TRUNC(sysdate), -2);

(Note: the first query would use an index on col2 but the second query would not; it would require a function-based index on TRUNC(col2) instead.)

Also, don't use TO_DATE on a column that is already a DATE or TIMESTAMP data type. TO_DATE takes a string as the first argument and not a DATE or TIMESTAMP so Oracle will perform an implicit conversion using TO_CHAR and if the format models do not match then you will introduce errors (and since any user can set their own date format in their session parameters at any time then you may get errors for one user that are not present for other users and is very hard to debug). db<>fiddle here

CodePudding user response：

Perhaps just:

select * 
from t1 
where col2 < add_months(sysdate, -2);