SQL Doubt

[Pol Appan]

I have problem with a sql query, it's returming ORA-01858: a non-numeric character was found where a numeric was expected
How can I resolve this? period_name is a Date field

select distinct to_char(period_name,'MON-YY') period_name from GL_SITE
where period_name >= '01/JAN/2005' and period_name <= to_char(sysdate,'DD/MON/YYYY')
order by to_date(period_name,'MON-YY') desc

[steve souza]

I don't know Oracle, but '01/JAN/2005' may not be of the right format. Try '01/01/2005'

[stu derby]

Originally posted by Cosmos Ja:
I have problem with a sql query, it's returming ORA-01858: a non-numeric character was found where a numeric was expected
How can I resolve this? period_name is a Date field

select distinct to_char(period_name,'MON-YY') period_name from GL_SITE
where period_name >= '01/JAN/2005' and period_name <= to_char(sysdate,'DD/MON/YYYY')
order by to_date(period_name,'MON-YY') desc

'01/JAN/2005' is not a date, it's a string, and Oracle has to implicitly convert it to a date. Using implicit conversion of dates in the database is a very bad coding practice, because:
1) different databases have different conversion rules
2) Oracle's conversion rules can be changed by the DBA making changes to settings, and then your code breaks
3) Oracle's conversion rules can be changed by changing session settings, such as NLS_LANGUAGE, and then your code breaks

Instead, use PreparedStatement, and bind a Java date into your query; then the database will recieve a date dataype and no implicit conversion will occur. (Or if that date is a constant, use a language-independent represenation such 01/01/2005, and the to_date fucntion.)

Date aDate = [...];
String SQL = "select distinct to_char(period_name,'MON-YY') period_name " +
             "from GL_SITE " +
             "where period_name >= ? " +
             "  and period_name <= to_char(sysdate,'DD/MON/YYYY') " +
             "order by to_date(period_name,'MON-YY') desc";
 
PreparedStatement ps = conn.prepareStatement( SQL );
ps.setDate (1, aDate);
 [...]