dimanche 1 novembre 2015

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?

Aucun commentaire:

Enregistrer un commentaire