aspose file tools*
The moose likes Beginning Java and the fly likes java.sql.Timestamp to Oracle date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "java.sql.Timestamp to Oracle date" Watch "java.sql.Timestamp to Oracle date" New topic
Author

java.sql.Timestamp to Oracle date

John Tyll
Greenhorn

Joined: Feb 06, 2003
Posts: 10
Hello,
I am new to java, I am selecting data from Oracle that is of type Date in Oracle but in java is java.sql.Timestamp format (2003-03-10 16:13:19.0) it is my rsltset.getTimestamp(3) variable, and I need to insert this into another Oracle table. When I am trying to insert this into an Oracle date column I get a Oracle 1861 error. (Granted I have other data I am inserting but it looks like this is the column causing the error). What's the easy way to fix this problem!!!
Gabriel White
Ranch Hand

Joined: Mar 02, 2003
Posts: 233
I need to see your insert statement, please use code tags. Normally you would use a getDate in your insert, but I need to see the code that is generating the error.
Peace out
John Tyll
Greenhorn

Joined: Feb 06, 2003
Posts: 10
This is the insert, need more info?
toAgent.executeUpdate("insert into EG_VENDOR (VENDOR_NUMBER, CREATION_DATE, INTERFACE_DATE,
INTERFACE_STATUS, VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, VENDOR_END_DATE_ACTIVE, VENDOR_SITE_ID,
VENDOR_SITE_CODE, VENDOR_SITE_END_DATE_ACTIVE, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3, CITY,
STATE, ZIP, AREA_CODE, PHONE, FAX_AREA_CODE, FAX_PHONE, VENDOR_CONTACTS, LAST_UPDATED_BY) values ( '"+
rs.getString(2) +"', '"+ rs.getTimestamp(3) +"', '"+ rs.getTimestamp(4) +"', '"+ rs.getString(5) +"',
'"+ rs.getString(6) +"', '"+ rs.getString(7) +"', '"+ rs.getTimestamp(8) +"', "+ rs.getInt(9) +", '"+
rs.getString(10) + "', '"+ rs.getTimestamp(11) +"', '"+ rs.getString(12) + "', '"+ rs.getString(13) +
"', '"+ rs.getString(14) + "', '"+ rs.getString(15) + "', '"+ rs.getString(16) + "', '"+
rs.getString(17) + "', '"+ rs.getString(18) + "', '"+ rs.getString(19) + "', '"+ rs.getString(20) + "',
'"+ rs.getString(21) + "', '"+ rs.getString(22) + "', '"+ rs.getString(23) + "')");

Thanks
John Tyll
Greenhorn

Joined: Feb 06, 2003
Posts: 10

sorry about the code tag...
Gabriel White
Ranch Hand

Joined: Mar 02, 2003
Posts: 233
Error: ORA 1861 Text: literal does not match format string.
This means that your literal (rs.timestamp) is not mapped to a format string. You need to include this date format in your insert statement by using the TO_DATE function. (This function will convert the string into the date)
For example:

Peace out bro
John Tyll
Greenhorn

Joined: Feb 06, 2003
Posts: 10
This is probably very easy but I keep getting the error "unclosed character literal". Given my code above how should I format your solution in quotes.
Thanks
Gabriel White
Ranch Hand

Joined: Mar 02, 2003
Posts: 233
John, I need to see how you added the new format in your insert statement. Please use the code tags.
Thanks bro
John Tyll
Greenhorn

Joined: Feb 06, 2003
Posts: 10
Sorry, I appreciate the help I had a long day of meetings...
toAgent.executeUpdate("insert into EG_VENDOR (VENDOR_NUMBER, CREATION_DATE, INTERFACE_DATE,INTERFACE_STATUS, VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, VENDOR_END_DATE_ACTIVE, VENDOR_SITE_ID,VENDOR_SITE_CODE, VENDOR_SITE_END_DATE_ACTIVE, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3, CITY,STATE, ZIP, AREA_CODE, PHONE, FAX_AREA_CODE, FAX_PHONE, VENDOR_CONTACTS, LAST_UPDATED_BY) values ( '"+rs.getString(2) +"', "+TO_DATE('"+ rs.getTimestamp(3) +"', 'DD-MON-YYYY HH24 :MI :SS') +", "+TO_DATE('"+ rs.getTimestamp(4) +"', 'DD-MON-YYYY HH24 :MI :SS') +", '"+ rs.getString(5) +"','"+ rs.getString(6) +"', '"+ rs.getString(7) +"', "+TO_DATE('"+ rs.getTimestamp(8) +"', 'DD-MON-YYYY HH24 :MI :SS') +", "+ rs.getInt(9) +", '"+rs.getString(10) + "', "+TO_DATE('"+ rs.getTimestamp(11) +"', 'DD-MON-YYYY HH24 :MI :SS') +", '"+ rs.getString(12) + "', '"+ rs.getString(13) +"', '"+ rs.getString(14) + "', '"+ rs.getString(15) + "', '"+ rs.getString(16) + "', '"+rs.getString(17) + "', '"+ rs.getString(18) + "', '"+ rs.getString(19) + "', '"+ rs.getString(20) + "','"+ rs.getString(21) + "', '"+ rs.getString(22) + "', '"+ rs.getString(23) + "')");

[ removed the code tag surrounding the big long unbroken line of code -ds ]
[ March 14, 2003: Message edited by: Dirk Schreckmann ]
Gabriel White
Ranch Hand

Joined: Mar 02, 2003
Posts: 233
Ok John, this is what I need from you:
The error code number (if there is one)
and
what the getTimestamp format physically looks like.
I was just giving you an example before, you weren't supposed to cut and paste it right into your code because your date format may be different. The example I gave you has to match the format of the getTimestamp (what format is it asking for)
Let me know bro
Peace out
John Tyll
Greenhorn

Joined: Feb 06, 2003
Posts: 10
Alright, I am basically selecting from one database and doing some stuff then inserting the exact information from the select to a new different database.
The date fields were giving me trouble, the date values look like this 2003-03-10 16:13:19.0. Here is my recent error message: The ^ is first located under the ' in '"+ rs.getTimestamp(3), then 11 more times throughout the code...
Compiler errors.
C:\EGATE\Client\bin\temp\15316\cr_Vendor_Transport.java:83: unclosed character literal
toAgent.executeUpdate("insert into EG_VENDOR (VENDOR_NUMBER, CREATION_DATE, INTERFACE_DATE, INTERFACE_STATUS, VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, VENDOR_END_DATE_ACTIVE, VENDOR_SITE_ID, VENDOR_SITE_CODE, VENDOR_SITE_END_DATE_ACTIVE, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP, AREA_CODE, PHONE, FAX_AREA_CODE, FAX_PHONE, VENDOR_CONTACTS, LAST_UPDATED_BY) values ( '"+rs.getString(2) +"', '"+ to_date('"+ rs.getTimestamp(3) +"','YYYY-MM-DD HH24:MI:SS') +"', '"+ to_date('"+ rs.getTimestamp(4) +"','YYYY-MM-DD HH24:MI:SS') +"', '"+ rs.getString(5) +"','"+ rs.getString(6) +"', '"+ rs.getString(7) +"', '"+ to_date('"+ rs.getTimestamp(8) +"','YYYY-MM-DD HH24:MI:SS') +"', "+ rs.getInt(9) +", '"+rs.getString(10) + "', '"+ to_date('"+ rs.getTimestamp(11) +"','YYYY-MM-DD HH24:MI:SS') +"', '"+ rs.getString(12) + "', '"+ rs.getString(13) + "', '"+ rs.getString(14) + "', '"+ rs.getString(15) + "', '"+ rs.getString(16) + "', '"+ rs.getString(17) + "', '"+ rs.getString(18) + "', '"+ rs.getString(19) + "', '"+ rs.getString(20) + "','"+ rs.getString(21) + "', '"+ rs.getString(22) + "', '"+ rs.getString(23) + "')");
^
12 errors
Compile Failed.
Dirk Schreckmann
Sheriff

Joined: Dec 10, 2001
Posts: 7023
All of those double quotes are maybe breaking apart your String literal unintentionally (so you should escape them). It's very difficult to help you if you don't post a more complete code example that replicates your problem. Please be sure to not have big long unbroken lines inside the code tags - scrolling left and right is a pain.
[ March 17, 2003: Message edited by: Dirk Schreckmann ]

[How To Ask Good Questions] [JavaRanch FAQ Wiki] [JavaRanch Radio]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java.sql.Timestamp to Oracle date
 
Similar Threads
Formatting Oracle Date data type insert
Java Datetime issue
Compiling using oracle JDBC drivers
Oracle Date and Time problem
Oracle date storage?