SQL 如何在Oracle中选择特定日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28248436/
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 select specific date in Oracle
提问by fanjavaid
I want to select just 1st and 20th date in Oracle Database. Here is my query.
我只想在 Oracle 数据库中选择第 1 个和第 20 个日期。这是我的查询。
SELECT INVOICE_DATE
FROM invoice_debt@stilng1_lngrpt2
WHERE INVOICE_DATE = TO_DATE('01', 'DD')
AND INVOICE_DATE = TO_DATE('20','DD')
AND INVOICE_DATE > TO_DATE('30-12-2010', 'DD-MM-YYYY')
AND INVOICE_DATE IS NOT NULL GROUP BY INVOICE_DATE
ORDER BY INVOICE_DATE DESC;
My query run this :
我的查询运行这个：
- Select just 1st date
- Select just 20th date
- Select date > 30-12-2010
- 只选择第一个日期
- 仅选择第 20 个日期
- 选择日期 > 30-12-2010
But nothing records returned.
但没有任何记录返回。
I try using EXTRACTto get Day of DATE, but i cannot using WHEREclause.
我尝试使用EXTRACT获取日期，但我不能使用WHERE子句。
SELECT
EXTRACT(DAY FROM TO_DATE(INVOICE_DATE, 'DD-MM-YY')) as DAY_EXTRACT
FROM invoice_debt@stilng1_lngrpt2;
Please help me thank you. :)
请帮帮我谢谢。:)
回答by psaraj12
Use the OR condition for dates since you cannot have same invoice_date to be 1st day and 20th Day and also use to_char for the invoice date since to_date would return only one date
对日期使用 OR 条件，因为您不能将相同的 invoice_date 设为第 1 天和第 20 天，并且还使用 to_char 作为发票日期，因为 to_date 只会返回一个日期
SELECT INVOICE_DATE
FROM invoice_debt@stilng1_lngrpt2
WHERE (to_char(INVOICE_DATE, 'DD')='01'
OR to_char(INVOICE_DATE,'DD')='20')
AND INVOICE_DATE > TO_DATE('30-12-2010', 'DD-MM-YYYY')
AND INVOICE_DATE IS NOT NULL GROUP BY INVOICE_DATE
ORDER BY INVOICE_DATE DESC;
回答by Lalit Kumar B
EXTRACT(DAY FROM TO_DATE(INVOICE_DATE, 'DD-MM-YY'))
EXTRACT(DAY FROM TO_DATE(INVOICE_DATE, 'DD-MM-YY'))
That makes no sense. You are converting a DATE to a DATE. I hope INVOICE_DATEcolumn is DATE data type. So just extract the day from it -
这是没有意义的。您正在将 DATE 转换为 DATE。我希望INVOICE_DATE列是DATE 数据类型。所以只需从中提取一天 -
extract(day from INVOICE_DATE)
extract(day from INVOICE_DATE)
You need an ORcondition for the 1st/20thday selection, and an ANDcondition to filter the dates with condition TO_DATE('30-12-2010', 'DD-MM-YYYY').
您需要一个OR条件来1st/20th选择日期，以及一个AND条件来过滤具有条件的日期TO_DATE('30-12-2010', 'DD-MM-YYYY')。
But, you need to make sure your query uses an efficient execution plan.
但是，您需要确保您的查询使用有效的执行计划。
- Using function TO_CHARover the date coumn will suppress any normal indexusage.
- 在日期参数上使用函数TO_CHAR将抑制任何正常的索引使用。
You need to have a function-based indexon to_char(INVOICE_DATE, 'DD')
你需要有一个基于函数的索引上to_char(INVOICE_DATE, 'DD')
WHERE (to_char(INVOICE_DATE, 'DD')='01'
OR to_char(INVOICE_DATE,'DD')='20')
AND INVOICE_DATE > TO_DATE('30-12-2010', 'DD-MM-YYYY'))
AND INVOICE_DATE IS NOT NULL
- Since you only want to get dates greater than year
2010, I think specifying a RANGEwill use the index on the date column to go for a INDEX RANGE SCAN.
- 由于您只想获得大于年份的日期
2010，我认为指定RANGE将使用日期列上的索引进行INDEX RANGE SCAN。
You could try these suggestions and examine the explain plan.
您可以尝试这些建议并检查解释计划。
