oracle 在oracle中查询日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19343225/
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
Querying datetime in oracle
提问by Shanna
I have a date type column in a table, where I store date along with time.
我在表中有一个日期类型列，我在其中存储日期和时间。
I want to query it by WHEREclause
I did it this way:
我想通过WHERE子句查询它我是这样做的：
select *
from conference_hall_book
where to_date(end_time,'dd/mon/yyyy hh24:mi:ss') <= to_date('26/oct/2013 15:00:00','dd/mon/yyyy hh24:mi:ss')
But the result has 27/10/2013 8:00:00 AM also in end_time column.
但结果在 end_time 列中也有 27/10/2013 8:00:00 AM。
Can any one help me finding the mistake?
谁能帮我找出错误？
回答by GTG
The problem occurs because of
出现问题的原因是
to_date(end_time,'dd/mon/yyyy hh24:mi:ss')
to_date(end_time,'dd/mon/yyyy hh24:mi:ss')
This is a wrong usage of the to_date function. To_date converts a string to a date. When Oracle sees this expression, it will automatically convert the end_time value to a string, using the configured date format of your database/session. This format typically doesn't include the time part, so a date with the value of "27/10/2013 8:00:00 AM" will be converted to the string "27/10/2013" (if your database date format is dd/mm/yyyy). Your to_date expression will then convert the string value "27/10/2013" back to a date. The resulting date value will be "27/10/2013 00:00:00", so you will have lost the time portion of your original date.
这是 to_date 函数的错误用法。To_date 将字符串转换为日期。当 Oracle 看到这个表达式时，它会使用数据库/会话的配置日期格式自动将 end_time 值转换为字符串。此格式通常不包含时间部分，因此值为“27/10/2013 8:00:00 AM”的日期将转换为字符串“27/10/2013”（如果您的数据库日期格式是 dd/mm/yyyy）。然后您的 to_date 表达式会将字符串值“27/10/2013”转换回日期。结果日期值为“27/10/2013 00:00:00”，因此您将丢失原始日期的时间部分。
The simple and correct solution is to drop the to_date(end_time) expression and just use end_time. This will also ensure that if you have index on end_time, the query will be able to use that index.
简单而正确的解决方案是删除 to_date(end_time) 表达式并只使用 end_time。这也将确保如果您在 end_time 上有索引，则查询将能够使用该索引。
select *
from conference_hall_book
where end_time <= to_date('26/oct/2013 15:00:00','dd/mon/yyyy hh24:mi:ss')
