• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Sql Server and Oracle - date

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)" +
"VALUES " +
"('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")
 
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
try
to_date(newdate)....
 
eammon bannon
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
www.hibernate.org - 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 ]
reply
    Bookmark Topic Watch Topic
  • New Topic