Click here to Skip to main content
12,897,945 members (84,605 online)
Rate this:
Please Sign up or sign in to vote.
See more: Mobile Android
i am developing an app where i need to get some data between two given 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);
            } 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
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:
(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.
azhar eqbal 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 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 28-Dec-12 8:14am
@OriginalGriff,Thank you i did what you suggested and achieved the desired result.Thanks Again
OriginalGriff 28-Dec-12 8:25am
You're welcome!
azhar eqbal 28-Dec-12 9:24am
@OriginalGriff,i had another problem,i would be thankful if you could help,below is the link...

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

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170424.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2017
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