File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Date format Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Date format" Watch "Date format" New topic

Date format

varkala prabhakar
Ranch Hand

Joined: Sep 08, 2001
Posts: 54
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+"";
java.sql.SQLException: ORA-00933: SQL command not properly ended
at oracle.jdbc.dbaccess.DBError.throwSqlException(
at oracle.jdbc.ttc7.TTIoer.processError(
at oracle.jdbc.ttc7.Oall7.receive(
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(
at oracle.jdbc.driver.OracleStatement.doExecute(
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(
at oracle.jdbc.driver.OracleStatement.executeQuery(
at AdminTransaction.doGet(
at javax.servlet.http.HttpServlet.service(
at javax.servlet.http.HttpServlet.service(
at sun.servlet.http.HttpServerHandler.sendResponse(
at sun.servlet.http.HttpServerHandler.handleConnection(
at Source)

but my table contains the records on that date. Very urgent to submit my work reply me...
Paulo Salgado
Ranch Hand

Joined: Jan 18, 2002
Posts: 98
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.
varkala prabhakar
Ranch Hand

Joined: Sep 08, 2001
Posts: 54
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;
---------- ---------- --------- ---------- --------- ---------- ----------
1154 kodati clerk 7839 15-JAN-02 2233 300

some more recors like
1151 mtadi clerk 7788 05-DEC-01 2140 300
1153 venkate analyst 2140 05-DEC-01 2000 20
1154 kodati clerk 7839 15-JAN-02 2233 300
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.
varkala prabhakar
Ranch Hand

Joined: Sep 08, 2001
Posts: 54
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
I agree. Here's the link:
subject: Date format
It's not a secret anymore!