SQL Oracle：如何在 where 子句中按日期和时间进行过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8839008/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: How to filter by date and time in a where clause
提问by Bob
How can I do this:
我怎样才能做到这一点：
select *
from tableName
where SESSION_START_DATE_TIME > To_Date ('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi' )
SESSION_START_DATE_TIME is in the format '12/01/2012 13:16:32.000'
SESSION_START_DATE_TIME 的格式为“12/01/2012 13:16:32.000”
I tried where To_Date (SESSION_START_DATE_TIME, 'DD-MON-YYYY hh24:mi') > To_Date ('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi' )
我试过 where To_Date (SESSION_START_DATE_TIME, 'DD-MON-YYYY hh24:mi') > To_Date ('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi' )
but no matter what I try I get the error:
SQL command not properly formed
但无论我尝试什么，我都会收到错误消息：
SQL command not properly formed
回答by wweicker
In the example that you have provided there is nothing that would throw a SQL command not properly formederror. How are you executing this query? What are you not showing us?
在您提供的示例中，没有任何内容会引发SQL command not properly formed错误。你如何执行这个查询？你没有给我们看什么？
This example script works fine:
此示例脚本工作正常：
create table tableName
(session_start_date_time DATE);
insert into tableName (session_start_date_time)
values (sysdate+1);
select * from tableName
where session_start_date_time > to_date('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi');
As does this example:
就像这个例子一样：
create table tableName2
(session_start_date_time TIMESTAMP);
insert into tableName2 (session_start_date_time)
values (to_timestamp('01/12/2012 16:01:02.345678','mm/dd/yyyy hh24:mi:ss.ff'));
select * from tableName2
where session_start_date_time > to_date('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi');
select * from tableName2
where session_start_date_time > to_timestamp('01/12/2012 14:01:02.345678','mm/dd/yyyy hh24:mi:ss.ff');
So there must be something else that is wrong.
所以一定有其他的东西是错误的。
回答by Eddie Awad
If SESSION_START_DATE_TIME is of type TIMESTAMP you may want to try using the SQL function TO_TIMESTAMP. Here is an example:
如果 SESSION_START_DATE_TIME 是 TIMESTAMP 类型，您可能想尝试使用 SQL 函数 TO_TIMESTAMP。下面是一个例子：
SQL> CREATE TABLE t (ts TIMESTAMP);
Table created.
SQL> INSERT INTO t
2 VALUES (
3 TO_TIMESTAMP (
4 '1/12/2012 5:03:27.221008 PM'
5 ,'mm/dd/yyyy HH:MI:SS.FF AM'
6 )
7 );
1 row created.
SQL> SELECT *
2 FROM t
3 WHERE ts =
4 TO_TIMESTAMP (
5 '1/12/2012 5:03:27.221008 PM'
6 ,'mm/dd/yyyy HH:MI:SS.FF AM'
7 );
TS
-------------------------------------------------
12-JAN-12 05.03.27.221008 PM
回答by Mowgli
Put it this way
把它这样
where ("R"."TIME_STAMP">=TO_DATE ('03-02-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
AND "R"."TIME_STAMP"<=TO_DATE ('09-02-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS'))
Where
R is table name.
TIME_STAMP is FieldName in Table R.
其中 R 是表名。
TIME_STAMP 是表 R 中的 FieldName。
回答by Vadzim
Obviously '12/01/2012 13:16:32.000' doesn't match 'DD-MON-YYYY hh24:mi' format.
显然 '12/01/2012 13:16:32.000' 与 'DD-MON-YYYY hh24:mi' 格式不匹配。
Update:
更新：
You need 'MM/DD/YYYY hh24:mi:ss.ff' format and to use TO_TIMESTAMP instead of TO_DATE cause dates don't hold millis in oracle.
您需要 'MM/DD/YYYY hh24:mi:ss.ff' 格式并使用 TO_TIMESTAMP 而不是 TO_DATE 因为日期在 oracle 中不包含毫秒。
回答by ron tornambe
Try:
尝试：
To_Date (SESSION_START_DATE_TIME, 'MM/DD/YYYY hh24:mi') >
To_Date ('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi' )
