Working with date in SQLite for Android

Working with dates in Oracle for reporting is very easy ( Since Oracle recognizes the date as datatype). But SQLite does not recognize Date as a datatype, this causes an issue when when you have to query for records that fall in between a range in SQLite.


Oracle it is very easy, just use he to_date command –

Oracle query :

SELECT amount,purchase_date FROM table_z
WHERE purchase_date >= TO_DATE('21-MAY-2011’,’DD-MON-YYYY’)
  AND puchase_date  <  TO_DATE('21-JAN-2012','DD-MON-YYYY');

In SQLite date needs to be converted into appropriate format before storing it in the database.

While storing in the database the date can be converted into YYYYMMDD format and stored in the database as an integer. Storing in this format in the database will ensure that older dates will have lesser value than the later ones.

Eg: 21-MAY-2011 will have 20110521

      21-JAN-2012  will have 20120121

The integer 20110521 < 20120121

So the SQlite equivalent of the query will be

SQLite Query :

SELECT amount,purchase_date FROM table_z
WHERE purchase_date >= 20110521
  AND puchase_date  <  20120121

Source: StackOverflow