CODEWITHSUNDEEP
java
BigBug
BigBug

Reputation: 6290

Formatting dates for use in mysql BETWEEN statement?

If I want to format a String that contains a date in such a way that I can use it in the BETWEEN section of my mysql query how would I go about doing this? 

String str = "Tue Sep 17 01:48:10 EDT 2013" // this string is passed into my function in this format.

String select = "SELECT id, consumption, date_time FROM consumption_info WHERE date_time BETWEEN '2013-09-15 00:00:00' AND '2013-09-17 00:00:00' GROUP BY id";

the problem is I can't use str the way it is - it's not formatted the right way. I thought I could use:

Date temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(str );

String finalDate = temp.toString() - but even this does not return what I want...

Any ideas?

Upvotes: 0

Views: 116

Answers (4)

Jaydeep Patel
Jaydeep Patel

Reputation: 2423

You have to first parse the date from 

String str = "Tue Sep 17 01:48:10 EDT 2013"

Like

Date temp = new SimpleDateFormat("EEE MMM d HH:mm:ss zzz yyyy").parse(str );

And then use this date object

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
sdf.format(new Date(temp));

This will produce the correct format.

Upvotes: 2

vels4j
vels4j

Reputation: 11298

You need to specify timezone when parsing/formatting date string. Take a look at this code.

String str = "Tue Sep 17 01:48:10 IST 2013" ;
SimpleDateFormat sdf=new SimpleDateFormat("EEE MMM d hh:mm:ss zzz yyyy");
TimeZone tz = TimeZone.getTimeZone("Asia/Calcutta"); // for example, use your own
sdf.setTimeZone(tz);
Date date = sdf.parse(str);
SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM-dd 00:00:00");
sdf1.setTimeZone(tz);
String dateStr = sdf1.format(date);
String qryStr = "SELECT id, consumption, date_time FROM consumption_info 
     WHERE date_time BETWEEN '2013-09-15 00:00:00' AND '"+dateStr+"' GROUP BY id";

Upvotes: 0

Jayamohan
Jayamohan

Reputation: 12924

temp.toString() is Date objects default toString() method, it is not going to print in the format you are expecting.

In your case SimpleDateFormat has to be used twice. Once to convert the Date in String to be converted to java.util.Date object. Then the Date has to be converted bact to String in the format expected in SQL. You can achieve as below,

String str = "Tue Sep 17 01:48:10 EDT 2013";

SimpleDateFormat sdf1 = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", Locale.ENGLISH);
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

// String to date
Date tempDate = sdf1.parse(str);
// Date to required format in String
String tempStr = sdf2.format(tempDate);

System.out.println(tempStr);

Note: Explicitly specify the SimpleDateFormat locale to English in first parse, otherwise it will use the platform default locale which may not be English.

Upvotes: 1

Moritz Petersen
Moritz Petersen

Reputation: 13057

You should use a PreparedStatement instead, e.g.:

    Date d1 = ...;
    Date d2 = ...;
    PreparedStatement ps = con.prepareStatement("SELECT id, consumption, date_time " 
        + "FROM consumption_info WHERE date_time BETWEEN ? AND ? GROUP BY id");
    ps.setDate(1, d1);
    ps.setDate(2, d2);
    ResultSet rs = ps.executeQuery();

Note, that in this case, d1 and d2 are of type java.sql.Date.

Upvotes: 2

Related Questions