Months between

[mooks]

Hi,

I am trying to get the months between 2 dates, however I think there is something wrong with my format and I couldn't get the result.

Two date fields namely : due_date, sysdate (both of DATE type)

SELECT MONTHS_BETWEEN (
TO_DATE(due_date,'MM-DD-YYYY'),
TO_DATE(sysdate,'MM-DD-YYYY') ) "Months"
FROM table_name
/

ORA-01858: a non-numeric character was found where a numeric was expected

Can anyone help me with the format please? Thanks! :)

[marcio.68]

TO_CHAR (due_date,'MM-DD-YYYY')

This is a correct to convert date to character.

[chrisrlong]

But the question is : Why are you trying to convert sysdate to a date? It is already a date and no conversion is necessary. The question is more for due_date. If it is a string, and needs to be converted to a date, then the format could be at fault. You need to know exactly what format the string field is holding the date in. For example, if due_date is holding a string such as '01-JAN-99', and you try to convert it to a date using the format 'MM-DD-YYYY', you will get the error you specified, because it is expecting a numeric DD and instead found the characters 'JA'.

Basically, if both fields are already dates, no conversion function is needed.
If they are strings, make absolutely sure that your pattern matches the format of the actual string values.

The default format for Oracle is 'DD-MON-YY'. Meaning, if you say
DECLARE
l_string VARCHAR2(10);
BEGIN
l_string := sysdate;
DBMS_OUTPUT.PUT_LINE ( l_string);
END;

OUTPUT:
----------
13-DEC-00

Hope this helps,

- Chris

[rcalzone]

Simple...If both are already DATE type, command should be:
MONTHS_BETWEEN(SYSDATE,DUE_DATE)

If due date is in the past, result will be a positive number.
If due date is in the future, result will be a negative number.