Solved

Convert String to sql.Date

Posted on 2002-03-24
11
1,194 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:Bekkerus
11 Comments
 

Expert Comment

by:asnmis
ID: 6892502
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.
0
 

Author Comment

by:Bekkerus
ID: 6892651
asnmis,

 Can you show the code based on my example?
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 6892845
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?
0
 

Author Comment

by:Bekkerus
ID: 6892948
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.
0
 
LVL 2

Accepted Solution

by:
karlinsky earned 50 total points
ID: 6893422
Hi
Why don't you use a prepared statement:

// your sql with '?' place holders instead of the parameters
String sql="update CONTRACT set BEGIN_DATE = ?, END_DATE = ?   WHERE ID = ?";
// prepare a statement for the sql
PreparedStatement stmt=connection.prepareStatement(sql);
// convert your dates into Date objects
SimpleDateFormat df=new SimpleDateFormat("dd-MMM-yyyy",Locale.US);
Date beginDate=df.parse("02-Mar-2001");
Date endDate=df.parse("24-Mar-2001");
// bind parameters
stmt.setDate(1,new java.sql.Date(beginDate.getTime());
stmt.setDate(2,new java.sql.Date(endDate.getTime());
stmt.setString(3,cd.getId());
// execute the update
stmt.executeUpdate();

This way the JDBC driver handles all the DB specific formating. You just give it two java.sql.Date objects.
This approach also can improve performance if you use the same statement multiple times with different parameters (different dates).



Hope this helps.

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:sv1040
ID: 6893683


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
0
 

Author Comment

by:Bekkerus
ID: 6894839
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()+"' ");
0
 
LVL 92

Expert Comment

by:objects
ID: 6895229
Use placeholders as mentioned a few times above.
0
 

Expert Comment

by:sv1040
ID: 6895919
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
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7901056
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
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7962547
Per recommendation, force-accepted.

Netminder
EE Admin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now