This week's book giveaway is in the OCAJP 8 forum.
We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes sql date formatting problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "sql date formatting problem" Watch "sql date formatting problem" New topic

sql date formatting problem

Vivienne Lambe

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

"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.

I agree. Here's the link:
subject: sql date formatting problem
It's not a secret anymore!