how to fetch records between two timestamps in oracle?-CodePudding

I have a column of TIMESTAMP(6) datatype in oracle and have values like 2022-04-01 18:02:42 and i wanna fetch all the records that falls between two datetime. I tried like below but no luck,

select * from table where column BETWEEN '2022-04-01 18:02:42' and '2022-11-03 19:28:57' -- no records

tried this too, select * from table where column BETWEEN to_date('2022-04-01','yyyy-mm-dd') and to_date('2022-11-03','yyyy-mm-dd') -- a non-numeric character was found where a numeric was expected

and,

select * from table where column BETWEEN to_timestamp('2022-04-01','yyyy-mm-dd') and to_timestamp('2022-11-03','yyyy-mm-dd') -- a non-numeric character was found where a numeric was expected.

I want to fetch records falls in this dates!

Thanks.

CodePudding user response：

You need to convert the literal into DATE using TO_DATE and required format mask to compare the timestamp column with the input timestamp values.


SQL> CREATE TABLE t(A TIMESTAMP);

Table created.

SQL>
SQL> INSERT INTO t(A) VALUES(to_date('2022-04-10T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'));

1 row created.

SQL> INSERT INTO t(A) VALUES(to_date('2022-05-01T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'));

1 row created.

SQL> INSERT INTO t(A) VALUES(to_date('2022-03-01T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t;

A
----------------------------
10-APR-22 03.39.00.000000 PM
01-MAY-22 03.39.00.000000 PM
01-MAR-22 03.39.00.000000 PM

SELECT *
     FROM t
  WHERE A BETWEEN
            to_date('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS')
             AND
            to_date('2022-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS');

A
--------------------------------------------------------------------------
10-APR-22 03.39.00.000000 PM
01-MAY-22 03.39.00.000000 PM

CodePudding user response：

Use TIMESTAMP literals:

SELECT *
FROM   table_name
WHERE  column_name BETWEEN TIMESTAMP '2022-04-01 18:02:42'
                   AND     TIMESTAMP '2022-11-03 19:28:57';

or DATE and INTERVAL DAY TO SECOND literals:

SELECT *
FROM   table_name
WHERE  column_name BETWEEN DATE '2022-04-01'   INTERVAL '18:02:42' HOUR TO SECOND
                   AND     DATE '2022-11-03'   INTERVAL '19:28:57' HOUR TO SECOND;

or TO_TIMESTAMP:

SELECT *
FROM   table_name
WHERE  column_name BETWEEN TO_TIMESTAMP('2022-04-01 18:02:42', 'YYYY-MM-DD HH24:MI:SS')
                   AND     TO_TIMESTAMP('2022-11-03 19:28:57', 'YYYY-MM-DD HH24:MI:SS');

or TO_DATE:

SELECT *
FROM   table_name
WHERE  column_name BETWEEN TO_DATE('2022-04-01 18:02:42', 'YYYY-MM-DD HH24:MI:SS')
                   AND     TO_DATE('2022-11-03 19:28:57', 'YYYY-MM-DD HH24:MI:SS');