This content has been marked as final. Show 5 replies
977490 wrote:SYSDATE is DATE and you are passing a STRING to the trunc function. Thats the reason SYSDATE dint give you error and the above query gives error. Change it as
My below qry is working fine with sysdate but when m passing a date it's throwing an error to get the current qtr end date..
select add_months(trunc('20-dec-2012','q'),3) - 1 from dual
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
even after adding the to_date function between trunc and date...
Thnx in advance...
select add_months(trunc(to_date('20-dec-2012', 'dd-mon-yyyy'),'q'),3) - 1 from dual
Can you achieve it without to_date... why?
call requires that argument1 is a DATE datatype.
In your original code you were passing a VARCHAR2 datatype, so Oracle was trying to implicitly convert it to a DATE datatype, but problems existed becuase the string you provided wasn't in the correct format for the NLS settings on your database.
You are always better to explicitly ensure that the arguments to functions are the correct datatype.
Take a string literal of a known format and convert it to date using TO_DATE with the correct format string...
Take a string literal known to be using Oracle internal fixed format for date strings, and use the in built DATE function to indicate it's a DATE datatype
SQL> select add_months(trunc(to_date('20-DEC-2012', 'DD-MON-YYYY'), 'q'), 3)-1 from dual; ADD_MONTHS(TRUNC(TO_ -------------------- 31-DEC-2012 00:00:00
Or, Provide something that is already known to be a DATE datatype...
SQL> select add_months(trunc(date '2012-12-20', 'q'), 3)-1 from dual; ADD_MONTHS(TRUNC(DAT -------------------- 31-DEC-2012 00:00:00
SQL> select add_months(trunc(sysdate, 'q'), 3)-1 from dual; ADD_MONTHS(TRUNC(SYS -------------------- 31-MAR-2013 00:00:00