how to compare two dates stored as string in database

Question

4.00/5 (1 vote)

See more:

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,

XML

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

C#

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

azhar eqbal

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2012-12-27T04:08:00

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:

C#

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.

Posted 27-Dec-12 4:08am

OriginalGriff

Comments

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...
http://www.codeproject.com/Questions/517322/Howplustoplusstoreplusvaluesplusfromplusmultiplepl