CODEWITHSUNDEEP
androidsqlite
Cai
Cai

Reputation: 5323

How to get the closest dates in sqlite database to present date

I have an SQLite database with a table that holds a column full of dates. I need to get the date closest to the present date out of everything in that column, but I don't know how to build a query for this.

For example I have the following dates in the column

3/15/2015
3/31/2015
4/15/2015
4/30/2015
5/15/2015
5/30/2015

If the current date is 4/20/2015 I need it to get all rows that have the date.

EDIT

For clarification I need the only the upcoming dates not the past dates.

4/30/2015

Also my dates are stored as string. Can anyone help?

FINAL QUERY courtesy of Loc Ha

Cursor trythis(String Date) {
    SQLiteDatabase db = this.getReadableDatabase();
    String[] params = new String[]{String.valueOf(Date)};
    Cursor cur = db.rawQuery("SELECT  " + colCompID + " as _id," + colCompClass + "," + colName + "," + colPayDue + "," + colDateDue + " FROM " + viewComps + " WHERE " + colDateDue + "=" + "( SELECT MIN (" + colDateDue + ") FROM " + PAYMENTS + " WHERE " + colDateDue + ">=?);", params);
    return cur;
}

Upvotes: 0

Views: 700

Answers (1)

LHA
LHA

Reputation: 9655

Test this:

SELECT t.* FROM table t
WHERE t.date_col = 
  ( SELECT MIN (t2.date_col) FROM table t2
    WHERE t2.date_col >= ? );

? parameter will be passed with value is CURRENT date

IF date_col data type is Integer in SQLite, ? parameter will be passed with this value: System.currentTimeMillis()

Note: You may have to remove time info such as hour, minute, second, millisecond from System.currentTimeMillis()

Note 2: If you use String as Data type in SQLite, you have to format System.currentTimeMillis() into Date format "yyyy/MM/dd". If you use other formats such as M/d/yyyy --> You will have date String comparing issues. See issue below for M/d/yyyy format:

"5/15/2015".compareTo("11/30/2015") ---> Return 4 > 0
--> means "5/15/2015" > "11/30/2015" --- Wrong

Upvotes: 2

Related Questions