Skip to content
Advertisement

filter timestamp column in SQL Oracle

I have a table with a Timestamp column which I need to filter after ‘2020-08-26’, but every solution won’t work

RUN_TIME
2020-07-22 04:22:07
2020-07-22 04:34:07
2020-07-22 04:45:07
2020-07-22 04:50:07
2020-07-22 04:55:08

I tried the below queries:

WHERE CAST (RUN_DATE AS DATE) >= TO_DATE('2020-08-26', 'yyyy-mm-dd')

WHERE to_date(RUN_DATE, 'YYYY-MM-DD hh24:mi:ss') >= to_date('26-08-2020 23:59:59', 'YYYY-MM-DD hh24:mi:ss')

WHERE RUN_DATE >= TO_TIMESTAMP('26-08-2020 23:59:59')

Advertisement

Answer

Use a TIMESTAMP literal:

SELECT *
FROM   table_name
WHERE  run_time >= TIMESTAMP '2020-08-27 00:00:00';

or, use a DATE literal:

SELECT *
FROM   table_name
WHERE  run_time >= DATE '2020-08-27';

or, use TO_TIMESTAMP with a format model:

SELECT *
FROM   table_name
WHERE  run_time >= TO_TIMESTAMP( '2020-08-27', 'YYYY-MM-DD' );

or, use TO_DATE with a format model:

SELECT *
FROM   table_name
WHERE  run_time >= TO_DATE( '2020-08-27', 'YYYY-MM-DD' );

Which for the sample data:

CREATE TABLE TABLE_NAME ( run_time TIMESTAMP(6) );

INSERT INTO table_name ( run_time )
SELECT TIMESTAMP '2020-07-22 04:22:07' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-07-22 04:34:07' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-07-22 04:45:07' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-07-22 04:50:07' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-07-22 04:55:08' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-08-26 23:59:59.999999' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-08-27 00:00:00' FROM DUAL;

All output:

| RUN_TIME                  |
| :------------------------ |
| 27-AUG-20 00.00.00.000000 |

db<>fiddle here

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement