This week's book giveaway is in the Android forum.
We're giving away four copies of Head First Android and have Dawn & David Griffiths on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Sql Server and Oracle - date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Sql Server and Oracle - date " Watch "Sql Server and Oracle - date " New topic

Sql Server and Oracle - date

Carty Ellis

Joined: Oct 04, 2003
Posts: 20
Here is a good challenge. I have a program which needs to update a column - DATE type in Oracle, datetime type in SQL Server. I currently am able to get the newDate, with proper value, from this piece of code:
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
long time= System.currentTimeMillis();
java.sql.Date systemsqldate=new java.sql.Date(time);
java.util.Date dateString=(java.util.Date)systemsqldate;
String newDate = null;
newDate = sdf.format(dateString);

to look fine in SQL Server. In Oracle I get the infamous ORA-01861: literal does not match format string. I really want this program to function by just switching drivers and connection statements. I do not want to parse actual code based upon the DB. Just for additional color, It will also need to work with DB2.
Any great minds out there that have successfully faced this one?
[ March 18, 2004: Message edited by: Carty Ellis ]I am using PreparedStatement to avoid DB sensitive issues - but it isn't working that way.
[ March 18, 2004: Message edited by: Carty Ellis ]
eammon bannon
Ranch Hand

Joined: Mar 16, 2004
Posts: 140
You should use a java.sql.Date type rather than trying to second-guess the date format for a particular DB. (Are you using it as a Date object or a String? You don't include the code you are trying to execute against the DB so its hard to tell) If you have to support this across three flavours of DB, why not try a persistance layer like Hibernate?
[ March 18, 2004: Message edited by: eammon bannon ]
Carty Ellis

Joined: Oct 04, 2003
Posts: 20
I am using a string for the date. Here is the statement using newDate:
PreparedStatement statementInsertDate = conn.prepareStatement(
"INSERT INTO tca_master " +
"(instidq, instid, instcd, source_id," +
"source_cd, dpmask, fyt, lyt, iname," +
"univ_dpmask, dinstid, dinstcd, tastatus," +
"last_mod_user, last_mod_date)" +
"('73', 'MIAMI', 'CAE'," +
"'002463 ',' '," +
"' ', '200402', '200206', 'ELOM UNIVERSITY'," +
"' ', ' ', ' ', ' '," +
"'CAE', '" + newDate + "')");

I am sure the approach is incorrect - I looked at NLS-DATE-FORMAT in the Oracle DB (we have no input on how that is set) and on that DB it is DD-Mon-YY so the approach I was considering is doomed to failure.
"Hibernate" is a new word to me. Other than Google searches - can you suggest a good direction to learn the concept? (It's not mentioned in "Head First Java")
Gowrishankar Mudaliar
Ranch Hand

Joined: Oct 20, 2001
Posts: 39
eammon bannon
Ranch Hand

Joined: Mar 16, 2004
Posts: 140 - its a proprietary ORM persistance layer that you can delegate this sort of cross DB platform work. It lets you manipulate relational data as Objects. In this instance with Hibernate, you'd give your PreparedStatement a java.util.Date object, and Hibernate will format it correctly for each DB you are using.
(BTW: watch out for Gowrishankar's suggestion - DB2 doesn't have a to_date function)
[ March 18, 2004: Message edited by: eammon bannon ]
I agree. Here's the link:
subject: Sql Server and Oracle - date
It's not a secret anymore!