I have a table in oracle(8i) which contains a column with type DATE. If I want to insert something in this column through CMP, and I use java.sql.Timestamp for this column, it works perfectly. But if I retrieve the value of this field through CMP, it gives the Timestamp object whose format is quite different from the regular Timestamp object.

The timestamp object returned from the CMP is :
4-04-10 10:00:00.0
And the timestamp object created from system time, - new java.sql.Timestamp( System.currentTimeMillis() ) is
2004-04-12 17:26:24.03

Plus, if I try to get the milliseconds from the Timestamp object returned from the CMP, it prints a large negative value.

In my application, I want to compare the timestamp object created from the current system time with the one returned from the CMP bean.

What should be the solution to this?

Sounds like the Timestamp class used by your server is buggy. Try using the Date.compareTo() method to see if that works. If not:

1) Convert the CMP Timestamp to a string via toString().

2) Convert the string to a Date using java.text.SimpleDateFormat. Based on the examples you provided, I can't tell what the Month/Day/Year order is, but I am betting: "Y-MM-dd HH:mm:ss".

I am inserting the records in the database manually by firing "insert into" statements at the SQL prompt and passing SYSDATE as the value of the date field. And yeah, the CMP is actually giving me the year as 0004 instead of 2004. Could that be a problem with the application server? I am using jboss 2.4. Here, while inserting, if I use only date (without time) - trunc(SYSDATE), and if I change the CMP field type to java.sql.Date, rather than java.sql.Timestamp, then it gets correct format from the CMP.

The problem could be with JBoss or the Oracle JDBC Driver. I java.sql.Date works, use that.

Oracle DATE type does not store nanoseconds, but Oracle TIMESTAMP type does.

Thus, if you are stuffing in a java.sql.Timestamp into a DATE field in the DB, you will get weird behavior when you compare what you stuck in with what you get back, i.e. equals will fail because the nanoseconds are off. Unless you just *happen* to be lucky enough to create a new timestamp when the nanos are
zero ;-) If you look at the long values of the times for objects you are trying to compare, you will notice that the last two digits of what comes back from the DB are always zero, no matter what you stuff in, if the Oracle column is of type DATE.

I got around this in my current project by creating a tiny utility method that gets a new instance of a GregorianCalendar, set the date to System.currentTimeMillis(), and set the milliseconds/nanos to 0, and get the date from the calendar. I wish there was some easier way to manipulate Dates in java, ha. ha. ha. I've been saying that for 9 years now. Dates, they sorely vex me.

Bill Bohrer
Nimble Consulting