0

in my database , dates are stored in DD-mm-yyyy format , how can i sort this to obtain the earliest date ? 

Cursor c = myDb.query(TABLE, new String[]{"dob"}, null, null, null, null, "dob");

I have selected it to order by dob field but its not ordered ... This is the output for the above query

01-03 17:14:51.595: VERBOSE/ORDER DOB(1431): 01-11-1977
01-03 17:14:51.595: VERBOSE/ORDER DOB(1431): 01-12-1988
01-03 17:14:51.614: VERBOSE/ORDER DOB(1431): 15-01-1977
01-03 17:14:51.656: VERBOSE/ORDER DOB(1431): 31-01-1988
Improve this question
2
  • 1
    It would help a lot if you could say what database you are using and what the variable type of the dates is in your database. But you could probably just use SQL to order your dates.
    – Sebastiaan van den Broek
    Commented Jan 3, 2011 at 11:25
  • sorry my bad , have edited my question , trying to query from SQlite in android
    – manu
    Commented Jan 3, 2011 at 11:48

2 Answers 2

Reset to default
1

In your case with sqlite and (afaik) it's lack of a date type, I would store the dates in epoch value in UTC time (this is a good practise anyway since you'll be timezone-independent) and then you can just sort by lowest value using ORDER BY.

You can just convert back to a readable date in your Java application (use SimpleDateFormat for example)

Date date = new Date(yourEpochValue);
SimpleDateFormat smf = new SimpleDateFormat("dd-MM-yyyy");
smf.setTimeZone(yourTimeZone);  
String dateString = smf.format(date);

You'd go about storing the value the other way around (if this is something you need to do)

Improve this answer
2
  • Found this post stackoverflow.com/questions/3557972/… , so i changed it to YYYY-MM-DD format , now order by works ,but this is just a temp solution :(
    – manu
    Commented Jan 3, 2011 at 12:10
  • Yeah that works, sort of. But look into NullUserException's answer, he's recommending the same thing as I did.
    – Sebastiaan van den Broek
    Commented Jan 3, 2011 at 12:13
1

If you are using SQL database and store dates as type DATE or DATETIME it does not matter how the dates are shown. You can just use the sql order by statement. If my assumptions are wrong please provide more details.

Improve this answer
2
  • have edited my question , am working in android with sqlite , tried ordering using order by but that s not proper so i got confused , does sqlite support date field ? i am using it as a text field
    – manu
    Commented Jan 3, 2011 at 11:50
  • I am sorry, I am not a guru in sqlite on Android, so please refer the documentation. But as a work around you can always sort your collection in java using Collections.sort() that accepts comparator. Create your custom object that holds collection of data and comparator that knows to compare 2 such objects.
    – AlexR
    Commented Jan 3, 2011 at 11:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.