Months between
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Months between

  1. #1
    Join Date
    Jan 2000
    Posts
    387
    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! :)

  2. #2
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97

    Lightbulb

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

    This is a correct to convert date to character.
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  4. #4
    Join Date
    Dec 2000
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width