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
prove to us that it doesn't work, even with the proper to_date
Here's my proof that it does:
Version 22.214.171.124, Windows 64-bit
SQL> select add_months(trunc(to_date('20-DEC-2012', 'DD-MON-YYYY'), 'q'), 3)-1 f rom dual; ADD_MONTH --------- 31-DEC-12
Edited by: 977490 on Jan 25, 2013 2:16 AM
In your reply that you just edited to say "thnx"
No, you should not do it without to_date. You have a string, not a date. Oracle will pick up the default date format from NLS settings, which may not be appropriate for your string.
ALWAYS use to_date when you need a date and you have a string
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