Oracle sql for records with a timestamp that sits between two timestamps

Question

I have two timestamps in the format String 2015-05-06T15:39:00 and 2015-04-06T15:39:00 .

What is an SQL query for Oracle that I can query all the records in a table with a timestamp that falls within this range.

Answer 1

And with an alternative way, you can use

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

Answer 2

 SELECT * FROM yourTable WHERE timestamps >= TO_DATE('2015-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS') AND timestamps <= TO_DATE('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS') 

Answer 3

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

Customization

 SQL> CREATE TABLE t(A TIMESTAMP); Table created. SQL> SQL> INSERT INTO t(A) VALUES(to_date('2015-04-10T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS')); 1 row created. SQL> INSERT INTO t(A) VALUES(to_date('2015-05-01T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS')); 1 row created. SQL> INSERT INTO t(A) VALUES(to_date('2015-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-15 03.39.00.000000 PM 01-MAY-15 03.39.00.000000 PM 01-MAR-15 03.39.00.000000 PM 

Query

 SQL> SELECT * 2 FROM t 3 WHERE A BETWEEN 4 to_date('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS') 5 AND 6 to_date('2015-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'); A -------------------------------------------------------------------------- 10-APR-15 03.39.00.000000 PM 01-MAY-15 03.39.00.000000 PM 

So, I got the required rows as the desired result.

Answer 4

None of the above answers worked for me.

however, first just replace the 'T' you have '' (space) and use below the query that worked for me

  select * from tb1 where timestamps BETWEEN TO_DATE ('2015-05-06 15:39:00', 'YYYY-mm-dd HH24:MI:SS') AND TO_DATE('2015-04-06 15:39:00', 'YYYY-mm-dd HH24:MI:SS'); 

Answer 5

Below is the time stamp, and you can change it within the required time.

 SELECT * FROM tbl1 WHERE timestamp BETWEEN to_date('21/11/2017 23:59:59','dd/MM/rrrr hh24:mi:ss') AND to_date('21/12/2017 15:59:59','dd/MM/rrrr hh24:mi:ss');`