SQL 如何根据oracle数据库中的日期检索记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14670657/
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
How to retrieve the records based on a date from oracle database
提问by user1658567
I have a table with date column in it. I need to fetch the records from it based on the given date.
我有一个带有日期列的表格。我需要根据给定的日期从中获取记录。
Currently when i used the query:
目前当我使用查询时：
select * from workingemployee_data where created_date like '20-Jan-2012'
I am getting those records which have created_date on 20-Jan-2012
我正在获取那些在 2012 年 1 月 20 日创建的记录
But i want to get the records those were created 10 days earlier to a given date (i.e) 20-Jan-2012.
但是我想获取在给定日期（即 20-Jan-2012）之前 10 天创建的记录。
Please suggest me on this.
请给我建议。
回答by mvp
This gives all records between today and 10 days ago:
这给出了今天和 10 天前之间的所有记录：
SELECT *
FROM workingemployee
WHERE created_date BETWEEN sysdate - INTERVAL '10' DAY
AND sysdate
This gives all records entered exactly 10 days ago:
这给出了 10 天前输入的所有记录：
SELECT *
FROM workingemployee
WHERE created_date = sysdate - INTERVAL '10' DAY
Replace sysdatewith exact date if you want.
sysdate如果需要，请替换为确切日期。
回答by A.B.Cade
- Why do you use
likeand not=? - Assuming that
created_dateis of typeDATE, it's bad practice to rely on implicit conversion according toNLS_DATE_FORMAT(this is what happens when you compare a date and a string) dd-mon-yyyyisn't a good format for querying since it deffers according toNLS_LANGUAGEbetter usemmfor months numbers
- 你为什么使用
like而不是=？ - 假设它
created_date是 typeDATE，依赖于隐式转换是不好的做法NLS_DATE_FORMAT（这是比较日期和字符串时发生的情况） dd-mon-yyyy不是查询的好格式，因为它根据月数的NLS_LANGUAGE更好使用mm而推迟
So, either use @mvp's answeror do something like this:
因此，要么使用@mvp 的答案，要么执行以下操作：
SELECT *
FROM workingemployee
WHERE trunc(created_date) = to_date('20-01-2013', 'dd-mm-yyyy') - 10
回答by ankush
SELECT * FROM workingemployee WHERE created_date > sysdate - INTERVAL '10' DAY;
SELECT * FROM workingemployee WHERE created_date > sysdate - INTERVAL '10' DAY;
