Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    46

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

    Hi,

    I am creating a view and in a view i am calculatinf the Employee Experience in Years and Month using months_between function and i am getting following Error:


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

    Below is the Query which i am writing:

    SELECT emp.employeeid, emp.employeecode,
    emp.firstname || ' ' || emp.lastname AS empname,
    dpt.department_name AS department,
    pm.position_description AS designation, bm.NAME AS branch,
    TRUNC ( MONTHS_BETWEEN (TO_DATE (SYSDATE, 'DD/MM/YYYY'),
    TO_DATE (emp.dateofjoining, 'DD/MM/YYYY')
    )
    / 12
    ) AS eyears,
    TRUNC (MOD (MONTHS_BETWEEN (TO_DATE (SYSDATE, 'DD/MM/YYYY'),
    TO_DATE (emp.dateofjoining, 'DD/MM/YYYY')
    ),
    12
    )
    ) AS emonth,
    gd.grade, emp.company_id, offi.worklocation_id
    FROM tbl_employees emp INNER JOIN tbl_officials offi
    ON emp.employeeid = offi.employeeid
    AND emp.isdeleted = 'N'
    AND emp.isactive = 0
    AND emp.issettlement = 0
    AND offi.isdeleted = 'N'
    LEFT JOIN tbl_department_master dpt ON dpt.department_id = offi.dept_id
    LEFT JOIN tbl_position_master pm ON pm.position_id =
    offi.designation_id
    LEFT JOIN tbl_branchmaster bm ON bm.ID = offi.worklocation_id
    LEFT JOIN tbl_grade gd ON gd.gradeid = offi.grade_id
    ;

    Please suggest me at the earliest.

    Regard's

    Prasad

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SYSDATE is a function that returns DATE datatype; you don't TO_DATE it.
    What datatype is "emp.dateof joining"? If DATE, the above applies to it as well. If CHARACTER (bad idea), values stored in that column do not look like "dd/mm/yyyy" (at least, some of them).

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    error at which line ..

    as show in the error .

Posting Permissions

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