wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL Doubt Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Doubt" Watch "SQL Doubt" New topic
Author

SQL Doubt

Pol Appan
Ranch Hand

Joined: Aug 26, 2004
Posts: 144
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


Never take anything for granted because you never know when you will lose it
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
I don't know Oracle, but '01/JAN/2005' may not be of the right format. Try '01/01/2005'


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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.)

 
 
subject: SQL Doubt
 
Similar Threads
DB2 Date format to Oracle Date format?
column not allowwd here - exception
Please help me format my date for Oracle/SQL
Column name too wide
Problem in using triggers and sequences