• 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
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Date format

 
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In my table i have the date column and i am using the setting SYSDATE to enter the date in my oracle 8i table. and when i try to get he records using on date field i get
SQL> select * from moneytrans where DOT=sysdate;
no rows selected
SQL> select * from moneytrans where DOT='1-JAN-02';
no rows selected
even i used java Date
java.util.Date d = new Date();
query ="select * from moneytrans where DOT="+d+"";
ERROR:
~~~~~~
java.sql.SQLException: ORA-00933: SQL command not properly ended
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java)
at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java)
at AdminTransaction.doGet(AdminTransaction.java:389)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:499)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at sun.servlet.http.HttpServerHandler.sendResponse(HttpServerHandler.java:165)
at sun.servlet.http.HttpServerHandler.handleConnection(HttpServerHandler.java:121)
at sun.servlet.http.HttpServerHandler.run(HttpServerHandler.java:90)
at java.lang.Thread.run(Unknown Source)

but my table contains the records on that date. Very urgent to submit my work reply me...
thanx
prabhakar.
 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Varkala,
I don't really know how Oracle handles DATE/TIMESTAMP types but I've worked with DB2 and I may somehow (hopefully) be helpful to you.
In your query
query ="select * from moneytrans where DOT="+d+"";
the Date object "d" will have its toString() method called to fulfil the concatenation operation. When that happens, your date object will be formatted to a String with the following format: dow mon dd hh:mm:ss zzz yyyy (day of week + month + dd and so...).
In other words your query will look like:
query = select * from moneytrans where DOT=Fri Jan 18 13:55:02 ET 2002;
I may be wrong here but this can be a character representation of a DATE that the DB manager is not configured to receive and translate into its internal Date representation, and therefore, you will get a result set with zero rows.
You may want to format your date string into a layout the DB manager will be able to handle. For DB2 the most used I've seen is YYYY-MM-DD, but that dependes on the DB manager and also on its setup. You may want to check with your DBA with character representation of dates your Oracle DB is ready to accept and translate into a real date.
If Oracle accepts the YYYY-MM-DD format you may try to use java.sql.Date instead of java.util.Date. The reason is the toString() method of java.sql.Date will convert to this format directly. This is availble for Java 2, I'm not sure about JDK 1.1.
A final comment: since you are sending a character representation of a date you may want to put that between character delimiters when you setup your query string. For instance:
query ="select * from moneytrans where DOT=\""+d+"\"";
this would lead to
query = select * from moneytrans where DOT="2002-01-18"
You also have to check which is the character delimiter accepted by your DB manager. Double quotes are usual ones but I've seen single quotes too. For DB2 this depends on the setup.
Hope I was helpful.
Paulo.
 
varkala prabhakar
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hai paulo,
you u have those are not working for me. and Java.util.Date allows JDBC to identify this as a SQL TIME value.
between operations works fine but when i compate for a particular date then i get no records.

SQL> select * from emp where hiredate between '1-JAN-02' and SYSdate;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
1154 kodati clerk 7839 15-JAN-02 2233 300
20

some more recors like
~~~~~~~~~~~~~~~~~~~~~~~~~
1151 mtadi clerk 7788 05-DEC-01 2140 300
20
1153 venkate analyst 2140 05-DEC-01 2000 20
20
1154 kodati clerk 7839 15-JAN-02 2233 300
20
when query the data base :
SQL> select * from emp where hiredate='05-JAN-01';
no rows selected
i get no results.

kindly suggest a solution soon, dead line is very nearer to submit my work.
regards
prabhakar.
 
varkala prabhakar
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hai,
i dont understand how to solve my problem, retriving records comparing date will work for some records and for some records its not working. i get this problem from 3 days back and before that it used to work fine. i use oracle 8i in winnt server. I did not make any changes in oracle settings.
kindly reply
prabhakar.
 
This tiny ad is wafer thin:
New web page for Paul's Rocket Mass Heaters movies
https://coderanch.com/t/785239/web-page-Paul-Rocket-Mass
reply
    Bookmark Topic Watch Topic
  • New Topic