Representing MilliSeconds in JDBC using Oracle

[Rajesh Kumar]

Hai Friends
In my JDBC application i have to display date, and time along milliseconds.
I am using oracle database.
The below query will display date and time.
select to_char(d,'YYYY-MM-DD hh24:MI:SS') from datetest;

In Oracle can we show Milliseconds along with date and time ?
Just now i searched in oracle documentation.
and i didnot found the reference to represent milliseconds.
is it possiblt to represent the milli seconds in oracle ?
if anybody knows [query ]the format to show milliseconds also please tell me.
i need to represent milliseconds also in my application.
Thanks in advance
Yours
Rajesh

[Jesper Ottosson]

Perhaps this is precisely what you're trying to avoid having to do, but if I were you, I would query the database for the raw date column, then use ResultSet.getDate() to get the date, and then format it manually.

[Rajesh Kumar]

Dear Jesper Ottosson,
Please tell me how to use ResultSet.getdate() and how to format the database data accordingly.
I am new to Java
Thanks in advance
Yours
Rajesh

Originally posted by Jesper Ottosson:
Perhaps this is precisely what you're trying to avoid having to do, but if I were you, I would query the database for the raw date column, then use ResultSet.getDate() to get the date, and then format it manually.

[Jamie Robertson]

Rajesh:
I don't think the Oracle date datatype stores the time to the millisecond. From what I've read from my Oracle 8i manuals, there is no millisecond value stored in the database, so it wouldn't be possible to retrieve it. One work around could be to store a long value in an Oracle Number datatype that represents the java.util.Date in milliseconds.

long millis = 0;
java.util.Date today = new java.util.Date();
PreparedStatement ps = con.prepareStatement("insert into emp (id, hiredate, name) values (?, ?, ?)");
ps.setString(1, id);
ps.setLong(2,today.getTime());
ps.setString(3, "Jimmy");
ps.executeUpdate();
...
//retrieve the date
ps = con.prepareStatement("select name, hiredate from emp where hiredate < ?");
ps.setLong(1,today.getTime());
ResultSet rs = ps.executeQuery();
...
java.util.Date dbDate = new java.util.Date(rs.getLong(2));
//format date...etc

This is a workaround if you can't find the date solution. You may want to post this question in the javaranch Oracle forum for an Oracle expert opinion.
Jamie
[This message has been edited by Jamie Robertson (edited December 10, 2001).]
[This message has been edited by Jamie Robertson (edited December 10, 2001).]