Derby Date search format?
Hi
I'm using derby with my application. And problem is that when i search database with date every format its fine except'7/18/07' ( mm/dd/yy). it gives no result.
e.g.
select * from table where Date between '6/18/2007' and '7/18/2007'
its fine and give the result like2007/18/7...
but when i search like
select * from table where Date between '6/18/07' and '7/18/07'
there is no result.
how can i do that and how can i change the outcome from 2007/18/7 to 7/18/2007.
When inserting date i want to change the format from mm/dd/yyyy to dd/mm/yyyy or dd/mm/yy.
Any tips i will be greatfull.
[667 byte] By [
jawadaha] at [2007-11-27 11:03:32]
# 1
Use a PreparedStatement and it's setDate(int, Date) method and you won't have to worry about the date strings format.
# 2
>how can i do that and how can i change the outcome from 2007/18/7 to >7/18/2007.
See formatDate method
>When inserting date i want to change the format from mm/dd/yyyy to >dd/mm/yyyy or dd/mm/yy.
See parseDate method.
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Utils{
public static String DEFAULT_DATE_FORMAT="yyyy-mm-dd";
public static String formatDate(Date date, String format, String errorDate){
try {
DateFormat formatter = new SimpleDateFormat(format);
return formatter.format(date);
} catch (Exception e) {
e.printStackTrace();
return errorDate;
}
}
public static Date parseDate(String dateString, String format, Date errorDate){
try {
DateFormat formatter = new SimpleDateFormat(format);
return formatter.parse(dateString);
} catch (Exception e) {
e.printStackTrace();
return errorDate;
}
}
}
Hope That Helps
# 3
> >how can i do that and how can i change the outcome
> from 2007/18/7 to >7/18/2007.
>
> See formatDate method
>
> >When inserting date i want to change the format from
> mm/dd/yyyy to >dd/mm/yyyy or dd/mm/yy.
>
> See parseDate method.
>
> ... snipped code ...
>
> Hope That Helps
I'm sorry, but, hogwash.
PreparedStatement's setDate allows you to set an argument using a Date and ResultSet's getDate allows you to retrieve a Date from the ResultSet as a Date, so why write your own methods (that are liable to be error prone, or at the very least DB specific) when there are general purpose methods, provided by the Driver, for this exact purpose.
# 4
Hi
Sorry for the late reply
You guys give me some brilliant ideas. Here is how i got the date format like dd/mm/yy
DateFormat fmt = DateFormat.getDateInstance(DateFormat.SHORT, Locale.UK);
String date = fmt.format(new Date());
but with preparedstatement i'm getting problem with setDate(); method.
Heres the code:
calling the method sendData for putting dat into database:
sendData(new Date(), Integer.parseInt(jTextField3.getText()), jComboBox1.getSelectedItem().toString());
Here's the method sendData:
public void sendData(Date dt, Integer amt, String dep){
try {
PreparedStatement ps1 = cdb.connection.prepareStatement(
"INSERT INTO \"Income\" VALUES (?, ?, ?)");
ps1.setDate(1, dt);
ps1.setInt(2, amt);
ps1.setString(3, dep);
int i = ps1.executeUpdate();
} catch (Exception e){}
}
but it is giving the error:
sendData(java.sql.Date,java.lang.Integer,java.lang.String) in reception.Data cannot be applied to (java.util.Date,int,java.lang.String)
data.sendData(new Date(), Integer.parseInt(jTextField3.getText()), jComboBox1.getSelectedItem().toString());
any idea.
Thanks
# 5
You passed a java.util.Date object as the first parameter. It should be a java.sql.Date object instead. That's what the error message is telling you.
So you need to create a java.sql.Date object from the java.util.Date object you already have as your second step.
# 6
Hi
I've solved the problem but still i didn't get any data from data base.
code:
public void getData(Date date, Date date1) {
PreparedStatement ps = cdb.connection.PreparedStatement("select * from table where \"Date\" between (?) and (?)")
ps.setDate(1, date);
ps.setDate(2, date1);
rs = ps.executeQuery();
while(rs.next()) {
vector.addElement(rs.getObject(1));
}
}
in the same code if i replace setDate() with setString() and provide date as String then it retrieve the data but date format is yyyy/mm/dd:(
i don't get any data,i think its the problem with Derby, it is not accepting date format . Is there any other way like SQL statement with which i can get date style like mm/dd/yyyy.
thanks
