aspose file tools*
The moose likes JDBC and the fly likes sql date formatting problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql date formatting problem" Watch "sql date formatting problem" New topic
Author

sql date formatting problem

Vivienne Lambe
Greenhorn

Joined: Oct 01, 2004
Posts: 4
I'm trying to insert a date/time into table. I read in a date/time string from a log file, convert to date and attemt to insert, using prepared statement. I cant get format "30/09/2004 4:19:58 PM" to appear in table, only date. I have tried using Timestamp, but then i still am unable to insert. I have a deadline for today with this issue. I appreciate any help

debug output
============
in logDate = Sun Sep 26 13:27:55 EDT 2004
in sql_date = 2004-09-26 13:27:55.0
LogParser::insertEventRow failed : RA-01830: date format picture ends before converting entire input string

code
====
"INSERT INTO EventLog (EVENT_DATE,EVENT_URL,DOWNLOAD_SIZE) VALUES (to_date(?,'dd/Mon/yyyy:hh24:mi:ss'),?,?)");

SimpleDateFormat formatter = new SimpleDateFormat("dd/MMM/yyyy:hh:mm:ss");
Date logDate = null;
logDate = formatter.parse(value.toString());
java.sql.Timestamp sql_date = new java.sql.Timestamp(logDate.getTime());
System.out.println("in logDate = " + logDate);
System.out.println("in sql_date = " + sql_date.toString
pstmt.setTimestamp(1, sql_date);

[Bear edit: not so urgent]
[ October 01, 2004: Message edited by: Bear Bibeault ]
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Originally posted by Vivienne Lambe:

...
"INSERT INTO EventLog (EVENT_DATE,EVENT_URL,DOWNLOAD_SIZE) VALUES (to_date(?,'dd/Mon/yyyy:hh24:mi:ss'),?,?)");
...
java.sql.Timestamp sql_date = new java.sql.Timestamp(logDate.getTime());
...
pstmt.setTimestamp(1, sql_date);

[Bear edit: not so urgent]

[ October 01, 2004: Message edited by: Bear Bibeault ]


Hi Vivienne,

Well, as Bear indicated, saying your problem is urgent is not likely to get you a better or faster response. Most people who can help will do so anyway whether it is urgent or not.

But anyway, one thing I spot is that you are using Oracle to_date function in your SQL, this function takes two 'Strings' as arguments, the 2nd one the date format you are using, and the 1st one a String representing the date in the format specified by the 2nd argument.

So, instead of

pstmt.setTimestamp(1, sql_date);

You want to use
pstmt.setString(1, sql_date_string)
where sql_date_string is a String representing date in 'dd/Mon/yyyy:hh24:mi:ss' format.


Another option would be to use a java.sql.Timestamp in a statement like
"INSERT INTO EventLog (EVENT_DATE,EVENT_URL,DOWNLOAD_SIZE) VALUES (?,?,?)" , without the call to Oracle to_date function.

cheers
Sonny
 
 
subject: sql date formatting problem