ORA-30076 when trying to find days between dates

Question

I was trying to find the no of days between 2 dates. I tried running the below query and ended up with ORA-30076 error.

Query is,

select extract(day from (sysdate - to_date('2009-10-01', 'yyyy-mm-dd'))) 
 from dual

Error is,

Error at Command Line:1 Column:34
Error report:
SQL Error: ORA-30076: invalid extract field for extract source
30076. 00000 -  "invalid extract field for extract source"
*Cause:    The extract source does not contain the specified extract field.

But when i try to run

Select extract(day from sysdate) from dual;

i got correct output.

Please help if anyone knows the reason as why i am getting the above error.

You don't need extract, look at this: http://stackoverflow.com/questions/1646001/how-can-i-get-the-number-of-days-between-2-dates-in-oracle-11g — mavroprovato, Sep 16 '13 at 12:34
`sysdate - to_date('2009-10-01', 'yyyy-mm-dd')` is enough. No need for `extract`. — , Sep 16 '13 at 12:39

score 8 · Accepted Answer · answered Sep 16 '13 at 12:35

8

Subtracting one date from another doesn't return a date, it returns a number that represents the number of days difference so you can't use extract.

answered Sep 16 '13 at 12:35

DoctorMick

6,703
28
26

score 3 · Answer 2 · answered Sep 16 '13 at 12:37

sysdate - to_date('2009-10-01', 'yyyy-mm-dd') will give the difference in days between two dates - there is no need to use extract function. Moreover in this situation it would be incorrect usage of extract function simply because it would expect expression of date or interval datatype after from clause and result of sysdate - to_date('2009-10-01', 'yyyy-mm-dd') subtraction is of number datatype - cause of the error you are facing.

score 2 · Answer 3 · answered Sep 16 '13 at 12:36

2

This:

 sysdate - to_date('2009-10-01', 'yyyy-mm-dd')

returns a number, not a date. You can only extract days from dates.

answered Sep 16 '13 at 12:36

Dan Bracuk

20,699
4
26
43

ORA-30076 when trying to find days between dates

3 Answers3