CODEWITHSUNDEEP
oracle-databaseoracle11g
AznDevil92
AznDevil92

Reputation: 554

Formatting Date Error - Oracle 11g

Trying to write a SQL query to format a date output, but I am getting an error stating, 'a non-numeric character was found where a numeric is expected.'

Below is my SQL:

SELECT e.emp_num, emp_lname, emp_fname, sal_amount
FROM LGEMPLOYEE e
JOIN LGSALARY_HISTORY sh ON e.emp_num = sh.emp_num
WHERE sal_from = (SELECT MIN (to_date(sal_from,'dd-mon-yy'))
                FROM LGSALARY_HISTORY sh 
                WHERE sh.emp_num = e.emp_num)
ORDER BY e.emp_num;

Can anyone help to resolve this issue?

Upvotes: 0

Views: 71

Answers (2)

Multisync
Multisync

Reputation: 8787

Try to replace 

MIN (to_date(sal_from,'dd-mon-yy'))

with 

TO_CHAR(MIN (to_date(sal_from,'dd-mon-yy')), 'dd-mon-yy')

You're trying to compare VARCHAR2 with a DATE. Oracle uses an implicit types conversation using the following rule:

When comparing a character value with a DATE value, Oracle converts the character data to DATE.

Just an assumption: Oracle is trying to convert sal_from to a DATE using default NLS settings (session or database) and apparently fails (because the default date format is 'dd-mm-yy' for example)

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

This is why it's never a good idea to store date values in a varchar2 column. There is at least one row in your table where the character string in sal_from isn't in the format you expect. That's causing the to_date call to throw an error.

One way of isolating the problematic rows would be something like

CREATE OR REPLACE FUNCTION is_valid( p_str IN VARCHAR2, p_mask IN VARCHAR2 )
  RETURN VARCHAR2
IS
  l_date     DATE;
BEGIN
  l_date := to_date( p_str, p_mask );
  RETURN 'Y';
EXCEPTION
  WHEN others THEN
    RETURN 'N';
END;

and then

SELECT *
  FROM lgsalary_history
 WHERE is_valid( sal_from, 'dd-mon-yy' ) = 'N'

Upvotes: 0

Related Questions