asked on

Convert String to sql.Date

Hello experts,

I have an object that contains a date that I retrieve before I insert into an Oracle database. I am having problems converting the string to a date that oracle likes.

String bd = cd.getBeginDate(); //get date String
bd now contains a value like: 2001-02-03 00:00:00.0 //This format is always the case!!!

So I made a simpleDateFormat like this:
java.text.SimpleDateFormat fmt = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");

Now what I would like to do is put this date into a java.sql.Date object and insert it into Oracle.

I go like this:
java.util.Date bDate = fmt.parse(bd);
long temp = bDate.getTime(); //temp now contrains= 2001-02-03
java.sql.Date beginDate = new java.sql.Date( temp );

System.out.println("update CONTRACT set "+
" BEGIN_DATE = '"+beginDate+"', "+
" END_DATE = '24/MAR/2002', "+
" WHERE ID = '"+cd.getId()+"' ");

When running it I get this...

update CONTRACT set BEGIN_DATE = '2001-02-03', END_DATE = '24/MAR/2002', WHERE ID = '5'

ERROR:
java.sql.SQLException: ORA-01861: literal does not match format string

As you can see one date is a fixed string. I did that because I can see oracle accepts only a special format of date. How can I make this work to insert my date correctly?

asnmis

To specify a date format for the class SimpleDateFormat, use "yyyy.MM.dd" rather than "yyyy-mm-dd". In the context of SimpleDateFormat, "mm" indicates minutes rather than the month.

Option 2: use prepared statment instead and use setDate or setTimestamp. former required java.sql.Data and later java.sql.Timestamp.

Bekkerus

ASKER

asnmis,

Can you show the code based on my example?

Peter Kwan

Bekkerus,

Why are your BEGIN_DATE and END_DATE format is different? Your BEGIN_DATE format is "yyyy-mm-dd" while your END_DATE format is "dd/MM/yyyy". How do I know which is correct and which is wrong?

Bekkerus

ASKER

pkwan,

Ahh I did that to make it clear to show how oracle dates have to be formatted. It likes this format '24/MAR/2002'. But I thought that java.sql.Date would handle everything for me when I perform an insert or update??? It fails to insert with my beginDate. How can I fix this? I dont want to parse the string character by character unless it is a last resort.

Hard to image that others have not encountered String to Date conversion issues with Oracle. Hope someone knows how to fix it.

ASKER CERTIFIED SOLUTION

karlinsky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sv1040

One other option is to do the following.

update CONTRACT set BEGIN_DATE = TO_DATE('2001-02-03','YYYY-MM-DD'), END_DATE = TO_DATE('24/MAR/2002','DD/MONTH/YYYY') WHERE ID = '5'

Regards
SV1040

Bekkerus

ASKER

Well this is working. I go through a lot of effort to get it into the correct format. Does this look terrible or what?

java.text.SimpleDateFormat fmt = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");

//begin date
String dateholder = cd.getBeginDate();
java.util.Date bDate = fmt.parse(dateholder);
long temp = bDate.getTime();
java.sql.Date beginDate = new java.sql.Date( temp );

//end date
dateholder = cd.getEndDate();
java.util.Date eDate = fmt.parse(dateholder);
temp = eDate.getTime();
java.sql.Date endDate = new java.sql.Date( temp );

System.out.println("update CONTRACT set "+
" BEGIN_DATE = TO_DATE('"+beginDate+"','YYYY-MM-DD'), "+
" END_DATE = TO_DATE('"+endDate+"','YYYY-MM-DD'), "+
" WHERE ID = '"+cd.getId()+"' ");

Mick Barry

Use placeholders as mentioned a few times above.

sv1040

If you can get rid of the milliseconds part from the dates (using substring function), then you need not do so much of formatting. The below query is sufficient.

update CONTRACT set "+
" BEGIN_DATE = TO_DATE('"+ cd.getBeginDate() +"','YYYY-MM-DD HH:MI:SS'), "+
" END_DATE = TO_DATE('"+ cd.getEndDate(); +"','YYYY-MM-DD HH:MI:SS'), "+
" WHERE ID = '"+cd.getId()+"'");

Regards
SV1040

Venci75

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Answered by: karlinsky
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Venci75
EE Cleanup Volunteer

Netminder

Per recommendation, force-accepted.

Netminder
EE Admin