Java - convert date formatted String from SQL db to date object for comparison

Question

I have a string stored in a database (example: Sat Jul 09 14:20:31 EDT 2011)

This is stored as string (I am aware of SQLs date capabilities, but I'm not exploring that at this point).

I want to pull this strong and compare it with the current time using the Date.compare function

Date currentDate = new Date(); // the new current time value mCursor.getString(mCursor.getColumnIndex(TIMECOLUMN) //the old stored time value, pulled from the SQL database

From the database I have a string value already formatted as a proper date. But I don't have an obvious function to compare this to my current date as a date object.

I need to know if one is greater than, less than, or equal to, the other. The Date compare function will allow this, but the object needs to be a date object.

I have tried SimpleDateFormat seeded with the string's date format, but I get a parse exception "unparsable date", perhaps because it is already formatted?

Date currentDate = new Date(); SimpleDateFormat dateTest = new SimpleDateFormat("E M d HH:mm:ss z y"); if(currentDate.compareTo(dateTest.parse((mCursor.getString(mCursor.getColumnIndex(TIMECOLUMN))))) > 0)

returns java.text.ParseException: Unparseable date: Sat Jul 09 14:20:31 EDT 2011

I suspect my SimpleDateFormat seed is incorrect. Insight Appreciated

The exception means just that: the string you are trying to parse does not match the patter that you have set. Revisit SimpleDateFormat documentation, make a few tries and you will which is the problem. — SJuan76, Jul 09 '11 at 17:46
[Never use SimpleDateFormat or DateTimeFormatter without a Locale](https://stackoverflow.com/a/65544056/10819573) — Arvind Kumar Avinash, Jun 20 '21 at 17:39

score 6 · Accepted Answer · answered Jul 09 '11 at 17:44

6

I believe you just need to change the "M" to "MMM" in your format string:

SimpleDateFormat format = new SimpleDateFormat("E MMM d HH:mm:ss z y");

"M" is a potentially-single-digit value, e.g. 1, 10 etc. "MMM" is a short month name (e.g. "Jul", "Sep").

However, you should probably also specify the locale of the date format. I'd also strongly encourage you to break your currently huge statement into several bits to make it easier to understand and debug:

int timeColumn = mCursor.getColumnIndex(TIMECOLUMN);
String dbDateText = mCursor.getString(timeColumn);
Date dbDate = format.parse(dbDateText);

if (currentDate.compareTo(dbDate) > 0) {
}

I'd also encourage you to use Joda Time for date/time-related work, and to avoid storing date/time data as text in the database :)

answered Jul 09 '11 at 17:44

Jon Skeet

1,421,763
867
9,128
9,194

I agree, see http://download.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html: excerpt: **Pattern letters are usually repeated, as their number determines the exact presentation:** Maybe it's something like `EEE MMM dd HH:mm:ss zzz yyyy`. – home Jul 09 '11 at 17:47
+1 for the Joda Time suggestion. This really needs more mindshare. Of course, Joda uses the same format strings, so the question is applicable in either case. :) – Ray Toal Jul 09 '11 at 17:57
1

@Ray: I mention Joda Time in virtually every date/time answer I give on Java :) – Jon Skeet Jul 09 '11 at 17:59

score 2 · Answer 2 · answered Jul 09 '11 at 17:53

Yes the date format is incorrect. Here is a demo:

import java.text.SimpleDateFormat;
import java.util.Date;
public class Test {
    public static void main(String[] args) throws Exception {
        SimpleDateFormat df = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy");
        System.out.println(new Date());
        String string = "Sat Jul 09 14:20:31 EDT 2011";
        System.out.println(df.parse(string));
    } 
}

Java - convert date formatted String from SQL db to date object for comparison

2 Answers2

Linked