Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Android
i am developing an app where i need to get some data between two given dates.my dates are stored in database as string. i am using the following method but its not working,
 
public ArrayList<String> getTestDates(String fromDate,String toDate){
        ArrayList<String> arrTestResults = new ArrayList<String>();
        String selectQuery = "SELECT  TestDate FROM "  + TABLE_RESULTS + " WHERE TestDate BETWEEN " + fromDate +" AND "+ toDate ;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor result = db.rawQuery(selectQuery, null);
        if(result.moveToFirst()){
            do{
                arrTestResults.add(result.getString(result.getColumnIndex(DatabaseHelper.KEY_TESTDATE)));
            } while (result.moveToNext());
        } else {
            return null;
        }
        
        return arrTestResults;
 
    }
can you please tell what is wrong with this method or i should try something else...Thanks in Advance.Please let me know if am not clear enough
 
following is schema of database
 
public static final String KEY_TESTID = "TestId";
    public static final String KEY_TESTLOCATIONID = "LocationId";
    public static final String KEY_TESTLOCATIONNAME = "LocationName";
    public static final String KEY_TESTRESULT = "TestResult";
    public static final String KEY_TESTDATE = "TestDate";
    public static final String KEY_TESTTIME = "TestTime";
    static final String TABLE_RESULTS = "table_Results";
    private static final String DATABASE_CREATE_TABLE_RESULTS = "create table table_Results (TestId integer primary key autoincrement, "
            + "LocationId text not null, "
            + "LocationName text not null, "
            + "TestResult integer not null, "
            + "TestDate text not null, "
            + "TestTime text not null);";
Posted 27-Dec-12 4:03am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Simple: don't do it.
 
Stop storing dates in databases as strings. You can't properly do any testing or comparisons between strings containing dates, unless you have stored them in a very specific format:
yyyy/MM/dd
(with or without the slashes). If you store them in any other data format, you comparisons will always involve changing from a string to an actual date every time you try to use them. And to add to that problme, you have to convert them to a date format in order to present them to the user in his preferred format.
 
So store them as a DateTime (or a DateTime2, whichever) and you cna compare them without problems, and display them in whatever format your user prefers.
  Permalink  
Comments
azhar eqbal at 27-Dec-12 10:52am
   
@OriginalGriff,thank you for your valuable sugestion,can you please help me with some links of tutorials to achieve this.
OriginalGriff at 27-Dec-12 10:56am
   
You don't need tutorial, you need to change your database design to use a DateTime instead of a string based field.
You wouldn't use a string to store a double, would you?
azhar eqbal at 28-Dec-12 8:14am
   
@OriginalGriff,Thank you i did what you suggested and achieved the desired result.Thanks Again
OriginalGriff at 28-Dec-12 8:25am
   
You're welcome!
azhar eqbal at 28-Dec-12 9:24am
   
@OriginalGriff,i had another problem,i would be thankful if you could help,below is the link...
http://www.codeproject.com/Questions/517322/Howplustoplusstoreplusvaluesplusfromplusmultiplepl

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.140814.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100