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.
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.
Joined: Sep 08, 2001
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.
Joined: Sep 08, 2001
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.