Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t1 (
  col1 NUMBER,
  col2 TIMESTAMP(3)
);
INSERT INTO T1 (col1, col2)
SELECT 1, TIMESTAMP '2022-01-03 12:00:00.000' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '2022-01-03 13:00:00.000' FROM DUAL UNION ALL
SELECT 3, TIMESTAMP '2021-11-26 10:27:11.750' FROM DUAL UNION ALL
SELECT 4, TIMESTAMP '2021-11-26 10:27:59.606' FROM DUAL UNION ALL
SELECT 5, TIMESTAMP '2021-12-16 11:47:04.105' FROM DUAL UNION ALL
SELECT 6, TIMESTAMP '2021-12-16 12:29:27.101' FROM DUAL
6 rows affected

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


COL1
COL2


1
03-JAN-22 12.00.00.000 PM

2
03-JAN-22 01.00.00.000 PM

3
26-NOV-21 10.27.11.750 AM

4
26-NOV-21 10.27.59.606 AM

5
16-DEC-21 11.47.04.105 AM

6
16-DEC-21 12.29.27.101 PM


ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
SELECT TO_CHAR(ADD_MONTHS(sysdate, -2), 'YYYY-MM-DD HH24:MI:SS') AS dt FROM DUAL;


DT


2022-01-03 10:15:49


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


DT


2022-01-03 00:00:00


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


COL1
COL2


1
2022-01-03 12:00:00.000

2
2022-01-03 13:00:00.000

3
2021-11-26 10:27:11.750

4
2021-11-26 10:27:59.606

5
2021-12-16 11:47:04.105

6
2021-12-16 12:29:27.101


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


COL1
COL2


1
2022-01-03 12:00:00.000

2
2022-01-03 13:00:00.000

3
2021-11-26 10:27:11.750

4
2021-11-26 10:27:59.606

5
2021-12-16 11:47:04.105

6
2021-12-16 12:29:27.101

COL1	COL2
1	03-JAN-22 12.00.00.000 PM
2	03-JAN-22 01.00.00.000 PM
3	26-NOV-21 10.27.11.750 AM
4	26-NOV-21 10.27.59.606 AM
5	16-DEC-21 11.47.04.105 AM
6	16-DEC-21 12.29.27.101 PM

COL1	COL2
1	2022-01-03 12:00:00.000
2	2022-01-03 13:00:00.000
3	2021-11-26 10:27:11.750
4	2021-11-26 10:27:59.606
5	2021-12-16 11:47:04.105
6	2021-12-16 12:29:27.101