• 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

Need help with a query thats driving me insane

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a query that says:
String query = "SELECT count(*) as NUM_ORDERS " +"FROM web_order_header " +"WHERE CREATE_DATE >= '" + date1.trim() + "' AND CREATE_DATE <= '" + date2.trim() + "'";
where dates are in the format 01-jan-2003
when i try to run the statment through a stmt.executeQuery(query), i get an error saying
java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected
can someone please tell me what is wrong with this SQL statement? It seems to work fine using SQLNavigator... i'm really stuck
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You may find it easier to use a prepared statement. That way JDBC takes care of creating the proper date format.
Different databases have different formatting requirements for dates.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Oliver,
I presume that the variables "date1" and "date2" are instances of "java.lang.String". Oracle will try to convert a string literal (in an SQL statement) representing a date into the (database) DATE datatype -- based on the default date format.
Here is an excerpt from the Oracle9i SQL Reference, Release 2 (9.2) (Part Number A96540-02):


The default date format is determined implicitly
by the NLS_TERRITORY initialization parameter,
or can be set explicitly by the NLS_DATE_FORMAT
parameter.


The URL for the relevant Web page is:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions137a.htm#1003595
Assuming you haven't set the NLS_DATE_FORMAT parameter (since it is optional), but only the NLS_TERRITORY parameter (since it is mandatory), this Web page shows some of the default date formats for several values of NLS_TERRITORY:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch3.htm#49984
Alternatively, Oracle's (thin) JDBC driver supports the JDBC "escape syntax". More details are available here:
http://java.sun.com/j2se/1.4.1/docs/guide/jdbc/getstart/statement.html#999472
Example:

Your third alternative is to use "PreparedStatement" -- as Jeanne has suggested -- however, I believe she is implying that you need to convert "date1" (and "date2") from a "String" to a "java.sql.Timestamp" -- using the "java.text.DateFormat" class.
Example:

See the javadoc for more details:
http://java.sun.com/j2se/1.4.1/docs/api/java/text/SimpleDateFormat.html
(Using the "PreparedStatement" is left as an exercise ;-)
Hope this has helped you.
Good Luck,
Avi.
 
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When you pass the select statement you can include the date strings in the oracle to_date function so that oracle can process them as dates
database_dt > to_date( 'date string', 'yyyymmdd')
hope that helps
Dan
 
Oliver Ng
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks for all your replies.. i will try these out and see if it leads to a solution
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic