• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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.)

 
I suggest huckleberry pie. But the only thing on the gluten free menu is this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic