CODEWITHSUNDEEP
androidsqliteandroid-sqlite
NSouth
NSouth

Reputation: 5276

Querying SQLite Dates with different formats

I have dates in a SQLite table that are stored in non-standard date formats. I need to be able to query by them. For example, records for today's date are 11/1/2015 in the "date" column and 2015-11-1 in the "sortDate" column.

My query needs to return the count of records from the past week. The following returns nothing: SELECT count(*) FROM Grades WHERE sortDate BETWEEN '2015-10-24' AND '2015-11-02'

I also get nothing from SELECT count(*) FROM Grades WHERE sortDate BETWEEN datetime('now', '-7 days') AND datetime('now')

I think the issue is that my dates are not padded to always have 2 month or date digits, as in YYYY-MM-DD. How would I query this existing data with these non-standard formats?

Upvotes: 2

Views: 130

Answers (2)

NSouth
NSouth

Reputation: 5276

I ended up getting all date strings from the database and dealing with them in Java. I just needed the count of how many entries there were within the past week, past two weeks, and past month. I wrote the following function to return those counts based on a provided ArrayList of strings.

Calendar today = Calendar.getInstance();
        Calendar tomorrow = (Calendar) today.clone();
        tomorrow.add(Calendar.DATE, 1);

        Calendar backDateWeek = (Calendar) today.clone();
        backDateWeek.add(Calendar.DATE, -7);
        Calendar backDateTwoWeeks = (Calendar) today.clone();
        backDateTwoWeeks.add(Calendar.DATE, -14);
        Calendar backDateMonth = (Calendar) today.clone();
        backDateMonth.add(Calendar.DATE, -30);

        ArrayList<Calendar> calendarList = new ArrayList<Calendar>();
        Calendar tmpCal;
        String strSplit[];
        int month;
        int day;
        int year;
        int countWeek = 0;
        int countTwoWeeks = 0;
        int countMonth = 0;
        for (String dateStr : dateStrings) {
            strSplit = dateStr.split("/");
            month = Integer.parseInt(strSplit[0]) - 1;
            day = Integer.parseInt(strSplit[1]);
            year = Integer.parseInt(strSplit[2]);
            tmpCal = Calendar.getInstance();
            tmpCal.set(Calendar.YEAR, year);
            tmpCal.set(Calendar.MONTH, month);
            tmpCal.set(Calendar.DAY_OF_MONTH, day);

            if (tmpCal.after(backDateWeek) && tmpCal.before(tomorrow)) {
                countWeek++;
                countTwoWeeks++;
                countMonth++;
            } else if (tmpCal.after(backDateTwoWeeks) && tmpCal.before(tomorrow)) {
                countTwoWeeks++;
                countMonth++;
            } else if (tmpCal.after(backDateMonth) && tmpCal.before(tomorrow)) {
                countMonth++;
            }
        }

        int[] countsArray = new int[3];
        countsArray[0] = countWeek;
        countsArray[1] = countTwoWeeks;
        countsArray[2] = countMonth;

        return countsArray;

Upvotes: 0

Randyka Yudhistira
Randyka Yudhistira

Reputation: 3652

As Sqlite doesn't have a date type you will need to do string comparison to achieve this. For that to work you need to reverse the order - eg from dd/MM/yyyy to yyyyMMdd, using something like 

where substr(column,7)||substr(column,4,2)||substr(column,1,2) 
      between '20101101' and '20101130'

Upvotes: 1

Related Questions