oracle 从当前系统日期检索过去两年的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15861302/
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
retrieving last two years data from current sysdate
提问by Oracle rookie
Hi i am stuck on an oracle query wherein i want to retrieve data from previous two years based on the current year like this is 2013 so i want to retrieve all records from year 2011 and 2012 i have done the following query but m getting stuck on date formats i am pretty new to oracle here is the query
嗨，我被困在一个 oracle 查询上，其中我想根据当前年份检索前两年的数据，例如 2013 年，所以我想检索 2011 年和 2012 年的所有记录我已经完成了以下查询，但我被卡住了日期格式我对 oracle 很陌生，这里是查询
Select
to_date(stage_end_date,'yy') Years,SUBPRODUCT Product,sum(OFFER_COUNT) SumCount
,sum(offer_amount)SumAmount
from stage_amt
and
Offer_amount !=0
and
to_date(stage_end_date,'yy')
between to_char( sysdate, -2 ) and to_char( sysdate)
group by to_date(stage_end_date,'yy'),SUBPRODUCT
order by Years asc;
m getting this error ORA-00932: inconsistent datatypes: expected DATE got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s" can any one help me what to do
我收到此错误 ORA-00932：不一致的数据类型：预期日期得到 NUMBER 00932。00000 - “不一致的数据类型：预期 %s 得到 %s” 任何人都可以帮我做什么
regards
问候
回答by Ronnis
How about this?
这个怎么样？
where stage_end_date >= trunc(sysdate, 'yyyy') - interval '2' year
and stage_end_date < trunc(sysdate, 'yyyy');
回答by pratik garg
with sysdate you are appling to_char function but giving incorrect date formate -
使用 sysdate 您正在应用 to_char 函数但提供不正确的日期格式 -
I believe that stage_end_date is a date column, if so, I think you need to use to_char function in place of to_date in select query ... try below query -
我相信 stage_end_date 是一个日期列，如果是这样，我认为您需要在选择查询中使用 to_char 函数代替 to_date ...尝试下面的查询 -
Select To_Char(stage_end_date,'yy') Years,
SUBPRODUCT Product,
sum(OFFER_COUNT) SumCount,
sum(offer_amount)SumAmount
from stage_amt
where Offer_amount !=0
and to_number(To_char(stage_end_date,'YYYY')) between
to_number(to_char( sysdate,'YYYY'))-2 and to_number(to_char(sysdate,'YYYY'))
group by to_char(stage_end_date,'yy'),
SUBPRODUCT
order by Years asc;
Although Oracle will take care or data conversion from string to number while comparing but for safer side I have wrote TO_NUMBERfunction also while comparing dates.
尽管 Oracle 在比较时会注意或将数据从字符串转换为数字，但为了更安全，我TO_NUMBER在比较日期时也编写了函数。
