oracle 如何在oracle中选择两个日期之间或单个年/月/日之间的结果？(to_date, 提取, 系统日期)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2130973/
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 results between two dates or by single year/month/day in oracle? (to_date, extract,sysdate)
提问by neverMind
I have a table for orders and it has 2 column with DATE types: delivery_date and order_date. In my sqldeveloper, all the dates I've inserted via Java or by hand are in the format 10.01.25 for the 25th Jan of this year. When I try to see the the total_price for orders betweenone date and another, I force the format like this:
我有一个订单表，它有 2 列 DATE 类型：delivery_date 和 order_date。在我的 sqldeveloper 中，我通过 Java 或手动插入的所有日期都是今年 1 月 25 日的 10.01.25 格式。当我尝试查看一个日期和另一个日期之间订单的 total_price 时，我强制采用如下格式：
create or replace function calc_Outlays (init_date IN DATE,final_date IN date)return number is
v_total_enc FORNECIMENTO.TOTAL_ENC_FORNEC%TYPE;
begin
select f.total_enc_fornec into v_total_enc from fornecimento f where f.data_entrega between init_date and final_date;
return v_total_enc;
Exception
When no_data_found then
Insert Into errors Values (0,'No supplyment costs found for dates between '||to_char(init_date)||' and '||to_char(final_date),systimestamp);
return -1;
end;
but I get -1 returned. I even try to do the query like this: select f.total_enc_fornec from fornecimento f where f.data_entrega = '10.01.24';
like this: select f.total_enc_fornec from fornecimento f where f.data_entrega = to_date('10.01.24','yy-mm-dd');
like this: select f.total_enc_fornec from fornecimento f where f.data_entrega < to_date('10.01.24');and NOTHING is returned!
but if I execute : select f.total_enc_fornec from fornecimento f where f.data_entrega <= sysdate;it prints a result as it's supposed to...
但我得到 -1 返回。我什至尝试像这样进行查询：select f.total_enc_fornec from fornecimento f where f.data_entrega = '10.01.24';
像这样：select f.total_enc_fornec from fornecimento f where f.data_entrega = to_date('10.01.24','yy-mm-dd');
像这样：select f.total_enc_fornec from fornecimento f where f.data_entrega < to_date('10.01.24');没有返回任何东西！但是如果我执行：select f.total_enc_fornec from fornecimento f where f.data_entrega <= sysdate;它会打印出它应该的结果......
How can I do this? I'm clearly not passing correctly the parameters nor in the function, neither executing the query itself
我怎样才能做到这一点？我显然没有正确传递参数，也没有在函数中正确传递，也没有执行查询本身
Btw, and if I wanted to select ALL the orders of some year/month/day? Say, using extract function for example. Could you please show me how? I've tried, but I'm having the same problems and I it's simple in concept, at least, lol.
顺便说一句，如果我想选择某个年/月/日的所有订单？比如说，使用提取功能。你能告诉我怎么做吗？我试过了，但我遇到了同样的问题，而且我的概念很简单，至少，哈哈。
采纳答案by Jeffrey Kemp
The Oracle DATE type stores a date+time down to the nearest second - so if you say f.data_entrega = '10.01.24'it will not match a record where f.data_entregais 10.01.24 at 6am. SYSDATE, similarily, returns the current time as well as today's date.
Oracle DATE 类型将日期+时间存储到最接近的秒 - 所以如果你说它f.data_entrega = '10.01.24'不会匹配f.data_entrega10.01.24 at 6am的记录。SYSDATE 同样返回当前时间和今天的日期。
If you want your function to match on the date portion only (i.e. ignore any time values), you may need to change the function:
如果您希望您的函数仅匹配日期部分（即忽略任何时间值），您可能需要更改函数：
select f.total_enc_fornec into v_total_enc
from fornecimento f
where f.data_entrega between TRUNC(init_date) and TRUNC(final_date) + 0.99999;
