convert String date to Date

Hello,

I am trying to convert this string formatted date "2010-09-06"

to '06-SEP-10'

so the oracle database can query from it.

Could someone tell me how?

I used this but it is not working:

// where approvedDateFrom is passed in as "2010-09-06"

public void setApprovedDateFrom(String approvedDateFrom) throws ParseException {

SimpleDateFormat df = new SimpleDateFormat("DD-MMM-yy");


Date apprDate = df.parse(approvedDateFrom);
this.approvedDateFrom = apprDate;
}

Quote:

---

Originally Posted by computerbum

// where approvedDateFrom is passed in as "2010-09-06"
SimpleDateFormat df = new SimpleDateFormat("DD-MMM-yy");

---

Check your format string; DD-MMM-yy doesn't match a date in the format 2010-09-06.

kind regards,

Jos

Dates do not have a format.
DATE columns in a db do not have a format.

Use the setDate() of PreparedStatement to set the DATE values in your query.
This takes a java.sql.Date object, which you can create from a java.util.Date by doing "new java.sql.Date(utilDate.getTime())".

So, convert from the String to a util.Date as you are at the moment, then turn that into a sql.Date...

ETA: And I missed the formats didn't match...:)

I set the formmatting like so but it returns: Fri Jan 01 00:00:00 EST 2010

public void setApprovedDateFrom(String approvedDateFrom) throws ParseException {

SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd");


Date apprDate = df.parse(approvedDateFrom);
this.approvedDateFrom = apprDate;
}

Can you alter your SQL and use a setString instead of a setDate?

For example:
In your SQL you may have a date called update_date

Code:

---

SELECT col_name from table_name
WHERE update_date = TO_DATE(?, 'YYYY-MM-DD');

---

Ok, here is the issue I am running into:

When I pass my information to the database which is formatted like so:

SELECT t1.* FROM csc_rdmp t1 WHERE t1.rel_appr_date = '2010-09-01' AND t1.rel_appr_date <= '2010-09-17'

I get the error:

An error was encountered performing the requested operation

ORA-01861 literal does not match format string.


I know my problem is because it does not know how to handle the dates.

I need a way to handle the dates so I can perform queries base on the user supplied dates.

If rel_appr_date is an Oracle DATE column you should convert your Java Date variable to a String and do a setString for it. In Oracle you should always convert your variables to DATE columns, never let Oracle do the conversion for you because you are not always going to be 100% sure what the default DATE format is.

So,
SQL

Code:

---

SELECT t1.* FROM csc_rdmp t1 WHERE t1.rel_appr_date = [B]TO_DATE(?[/B], 'YYYY-MM-DD') AND t1.rel_appr_date <= [B]TO_DATE(?,[/B] 'YYYY-MM-DD')

---

Java
Assuming
apprDate is a Java Date variable.

Code:

---

DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String approvedDateFromAsString = formatter.format(apprDate);
setString(1, approvedDateFromAsString);
setString(2, approvedDateFromAsString);

---

Quote:

---

Originally Posted by SFNYC

If rel_appr_date is an Oracle DATE column you should convert your Java Date variable to a String and do a setString for it. In Oracle you should always convert your variables to DATE columns, never let Oracle do the conversion for you because you are not always going to be 100% sure what the default DATE format is.

So,
SQL

Code:

---

SELECT t1.* FROM csc_rdmp t1 WHERE t1.rel_appr_date = [B]TO_DATE(?[/B], 'YYYY-MM-DD') AND t1.rel_appr_date <= [B]TO_DATE(?,[/B] 'YYYY-MM-DD')

---

Java
Assuming
apprDate is a Java Date variable.

Code:

---

DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String approvedDateFromAsString = formatter.format(apprDate);
setString(1, approvedDateFromAsString);
setString(2, approvedDateFromAsString);

---

---

No.
No.
No.
And no.

You use a Date.

Do not convert your Date to a String, that is out and out wrong.

You use a PreparedStatement and the setDate() method. The ides of having a Date and then converting it to a String and then having the database convert it to a DATE again is just plain wrong.

To the OP, how are you creating your SQL? I hope you are using a PreparedStatement.
Please show use the whole code for the statement to execution.