• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

SQL Doubt

 
Ranch Hand
Posts: 144
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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'
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.)

 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic