我有两个字符串格式的时间戳:2015-05-06T15:39:00
和2015-04-06T15:39:00
。
对于Oracle而言,如何查询所有时间戳在这个范围内的记录?请提供相应的SQL查询语句。
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');
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')
Z
。 - Wernfried Domscheit以上答案都不适用于我。
然而,首先将您拥有的'T'替换为' '(空格),然后使用以下查询即可,这对我有效。
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');
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
查询
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
所以,我得到了我想要的输出所需的行。
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');`