I’m new to sql so it might not be too difficult for some of you but I simply need to find the length in years between a column displaying different dates and todays date. I’m not too sure what i’m doing wrong. It gives me the error: ORA-01858: a non-numeric character was found where a numeric was expected
This is the query
SELECT emp_lname || ', ' || emp_fname || ' ' || emp_initial AS "Employee Full Name",
emp_years,
TO_DATE('emp_hiredate','DD/MM/YYYY') - SYSDATE AS YEARS
FROM emp
Advertisement
Answer
Your immediate problem, the one throwing the error is this:
TO_DATE('emp_hiredate','DD/MM/YYYY')
You are telling the TO_DATE function that the character string literal ’emp_hiredate’ is a string representation of a date, in the format ‘DD/MM/YYYY’. That is:
the character string ’em’ is to be interpreted as day of the month; the character string ‘p’ is expected to be a ‘/’; the character string ‘_h’ is to be interpreted as the month; the character string ‘i’ is expected to be a ‘/’; the character string ‘reda’ is to be interpreted as the 4-digit year; and it doesn’t know what to do with ‘te’.
I think what you meant was
TO_DATE(emp_hiredate,'DD/MM/YYYY')
Notice that emp_hiredate is not enclosed in quotes, thus indicating “the value of the column emp_hiredate”.
But note that the function TO_DATE takes a string input and converts it to _oracle’s internal binary formate for DATE data types. So this assumes that emp_hiredate is a varchar2, string literal representation of a date, and further that said string literal representation of a date is in the format specified by the second argument of your call to TO_DATE. If that is the case, you have a serious design flaw. Dates should be stored as DATE, not VARCHAR2.
If emp_hiredate is already a DATE, then what you want is not TO_DATE, but TO_CHAR
TO_CHAR(emp_hiredate,'DD/MM/YYYY')
You need to go back to the SQL Reference manual and study the difference between TO_DATE and TO_CHAR. Also read this.