where子句中的日期格式-Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37180678/
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
Date format in where clause-Oracle
提问by Hemant Singh
I have one table where date column is having data in below format: "7/25/2014 12:14:27 AM'. I need to fetch this date by putting in the where clause. can anyone suggest how can i do this?
我有一张表，其中日期列的数据格式如下：“7/25/2014 12:14:27 AM'。我需要通过放入 where 子句来获取这个日期。有人能建议我怎么做吗？
回答by MT0
Dates (stored in tables) are represented by 7 bytes- they do not have any format associated with them. If they are formatted as a string then that is the client program which you are using to access the database applying its own formatting to the date (which you can usually set via the preferences in that program).
日期（存储在表中）由 7 个字节表示- 它们没有任何与之关联的格式。如果它们被格式化为字符串，那么这就是您用来访问数据库的客户端程序，将其自己的格式应用于日期（您通常可以通过该程序中的首选项进行设置）。
If the "date" is stored with a format then you are not storing it as a date but storing it as a string (i.e. VARCHAR2) format.
如果“日期”以某种格式存储，那么您不是将其存储为日期，而是将其存储为字符串（即VARCHAR2）格式。
Query - if the date is stored as a date:
查询 - 如果日期存储为日期：
SELECT *
FROM table_name
WHERE date_column = TO_DATE( '7/25/2014 12:14:27 AM', 'MM/DD/YYYY HH12:MI:SS AM' )
or, using ANSI/ISO literals:
或者，使用 ANSI/ISO 文字：
SELECT *
FROM table_name
WHERE date_column = TIMESTAMP '2014-07-25 00:14:27'
or, if you want values which are just for a given day then:
或者，如果您想要仅针对特定日期的值，则：
SELECT *
FROM table_name
WHERE date_column >= DATE '2016-05-12'
AND date_column < DATE '2016-05-13'
(This will allow you to use any indexes on the date_columncolumn.)
（这将允许您使用date_column列上的任何索引。）
or, only passing a single day value (represented by the bind variable :date_value):
或者，只传递一天的值（由绑定变量表示:date_value）：
SELECT *
FROM table_name
WHERE date_column >= :date_value
AND date_column < :date_value + INTERVAL '1' DAY
Query - if the "date" is stored as a string:
查询 - 如果“日期”存储为字符串：
SELECT *
FROM table_name
WHERE TO_DATE( date_as_a_string_column, 'MM/DD/YYYY HH12:MI:SS AM' )
= TIMESTAMP '2014-07-25 00:14:27'
or, just simply:
或者，只是简单地：
SELECT *
FROM table_name
WHERE date_as_a_string_column = '7/25/2014 12:14:27 AM'
回答by Codo
Most likely the date format you see is the format your SQL tool (SQLplus, SQL Developer, Toad etc.) uses. Proper date columns don't have an associated date format.
您看到的日期格式很可能是您的 SQL 工具（SQLplus、SQL Developer、Toad 等）使用的格式。正确的日期列没有关联的日期格式。
To write a reliable query for selecting by date, explicitly specify the date format in your query (otherwise Oracle we use the date format from your current session):
要编写按日期选择的可靠查询，请在查询中明确指定日期格式（否则 Oracle 我们将使用您当前会话中的日期格式）：
SELECT * FROM T1
WHERE DATE_COL > TO_DATE('7/25/2014 12:14:27 AM', 'MM/DD/YYYY HH:MI:SS AM');
Any other date format will also work:
任何其他日期格式也适用：
SELECT * FROM T1
WHERE DATE_COL > TO_DATE('2014-07-25 12:14:27', 'YYYY-MM-DD HH24:MI:SS');
