File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Doubt

 
Pol Appan
Ranch Hand
Posts: 144
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 862
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know Oracle, but '01/JAN/2005' may not be of the right format. Try '01/01/2005'
 
stu derby
Ranch Hand
Posts: 333
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.)

 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic