ORMLite query date format issue

Question

I want to convert sqlite query to Ormlite query.

SELECT * FROM Test where strftime('%m-%Y',Date)='11-2001'

I could not format date column like above query.

How to format Date column in Ormlite as MM-yyyy? Thanks.

Answer 1

If that is the exact SQL that you want to use then you can use the Where.raw(...) method:

QueryBuilder<Test, Integer> qb = testDao.queryBuilder();
qb.where().raw("strftime('%m-%Y',Date) = '11-2001'");
List<Test> results = qb.query();

However, this only seems to work if the date field is stored as a DATE_STRING type:

@DatabaseField(dataType = DataType.DATE_STRING)
Date date;

The issue is that by default the Xerial JDBC driver is storing the date in the format:

2012-07-19 09:58:18.36

Which does not [quite] match one of the approved Sqlite formats which are:

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

When you change it to DataType.DATE_STRING then it will be stored as the following which seems to work:

2012-07-19 10:03:49.000991

For more info, see the Sqlite docs on date functions. Unfortunately, the documentation does not fully explain that the database values need to be in a certain format:

http://www.sqlite.org/lang_datefunc.html