Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dates between different databases

 
Ben Roy
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic