aspose file tools*
The moose likes JDBC and the fly likes Dates between different databases Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Dates between different databases" Watch "Dates between different databases" New topic
Author

Dates between different databases

Ben Roy
Ranch Hand

Joined: Nov 01, 2000
Posts: 70
I'm trying to determine whether there is a standard format for passing dates that I can use. I have a project which is being developed on Jdatastore/IAS but is going to be deployed to oracle/weblogic at some point. I'm very concerned about dates, as there seems to be much confusion as to how they are handled.
I'm currently doing something like:
datefield = (Date'2000-12-4')
in my SQL statement. Is this going to work in oracle? I know microsoft products would just be:
datefield = '12/4/2000'
or just about any other format you could imagine, but if I try to do this without the (Date) it thinks I'm passing a string (ok so I am). Anyway, anyone have some insight? Is this going to tank on oracle? Is there some other standard way that will work between platforms?
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
One way to avoid this issue is to use java.sql.Date objects rather than worrying about the underlying format. If you can go this route, the database driver manages the formatting for you.
If, for some reason, you can't avoid putting the date string directly in your code, then its best to try and use the SQL standard format for dates. The SQL92 standard is YYYY-MM-DD. SQL99 uses the same format.
I know that oracle supports this format. For example, if you have a table with a data column, then this will correctly insert the date Dec 15, 2000:
insert into mytable (date_column) values ('2000-12-15');
Ben Roy
Ranch Hand

Joined: Nov 01, 2000
Posts: 70
Actually this is exactly the problem. If do that it throws an exception telling me that I was passing a string when I should have been passing a date. From what I've read on this board (and your comments) I think that's probably an error from the driver. But then comes the real question...when I change drivers is it going to change the required format? Or is this something that's being enforced on the "jdbc side" of the driver. Do you know what I mean? When I switch from the JDatastore JDBC driver to the Oralce Connection Pool JDBC driver is that enforcement going to be the same?
Edgar Sanchez
Greenhorn

Joined: Oct 12, 2000
Posts: 21
The documented way of passing dates in a string command is using date scape characters (this is a ODBC feature nicely cloned in JDBC), it looks like this:
INSERT INTO table (aNumber, aDateTime, aString) values (15, {ts '2000-12-18 23:45:12'}, 'Hello world')
A JDBC 1.2 compliant driver should convert the {ts 'yyyy-MM-dd HH:mm:ss'} into whatever format the underlying DB server is expecting, I've tried this in MS-SQL Server 7.0, Oracle 8.1 and MySQL and it works.
Best of luck,

[This message has been edited by Edgar Sanchez (edited December 18, 2000).]
Ben Roy
Ranch Hand

Joined: Nov 01, 2000
Posts: 70
One question. Will that work for a Date or Timestamp? The only reason I was suspicious was because of the "{ts". Sets off an alarm in my brain.
Edgar Sanchez
Greenhorn

Joined: Oct 12, 2000
Posts: 21
Actually you have three options:


  • {d ...} will take a date only, e.g. {d 2000-12-20} if the column
    is a datetime, then the time is set to 0:00:00
  • {t ...} will take a time only, if the column is a datetime, then the date is set to Jan 1 1970 (or today, I'm not sure, check it out)
  • {ts ...} will indeed set a datetime column, the ts has nothing to do with timestamp columns, I know its confusing, remember the standard was originally set up by Microsoft ;-)

  • Best luck
    Originally posted by Ben Roy:
    One question. Will that work for a Date or Timestamp? The only reason I was suspicious was because of the "{ts". Sets off an alarm in my brain.

Ben Roy
Ranch Hand

Joined: Nov 01, 2000
Posts: 70
Ok this isn't working. I'm using a JDatastore. I'm sending this query:
SELECT * FROM CALENDAR WHERE START_DATE = {d '2000-1-1'}
or
SELECT * FROM CALENDAR WHERE START_DATE = (d '2000-1-1')
The crazy part is that the first one (which is supposedly the way to do it) returns an error on the "{". It says it's an invalid character. If I use the "(" instead then I get an error after the "d" saying it was looking for ")" but found a "<string>" instead. Any thoughts?
Edgar Sanchez
Greenhorn

Joined: Oct 12, 2000
Posts: 21
Which version of ORACLE and of the ORACLE JDBC driver are you using?
Best luck,
Originally posted by Ben Roy:
Ok this isn't working. I'm using a JDatastore. I'm sending this query:
SELECT * FROM CALENDAR WHERE START_DATE = {d '2000-1-1'}
or
SELECT * FROM CALENDAR WHERE START_DATE = (d '2000-1-1')
The crazy part is that the first one (which is supposedly the way to do it) returns an error on the "{". It says it's an invalid character. If I use the "(" instead then I get an error after the "d" saying it was looking for ")" but found a "<string>" instead. Any thoughts?

Ben Roy
Ranch Hand

Joined: Nov 01, 2000
Posts: 70
Well, according to "Understanding the new SQL" the standard way to do it is:
DATE '2000-11-1'
That's what I've been using and it works fine, but I thought that was a JDatastore specific format. I guess that should work in Oracle as well.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Dates between different databases