Room Query | Between two string dates

Question

I don't understand why my Query is not working. I would like to get all items between two dates. I'm passing date like : "2020-05" but I've always zero results.

what am I doing wrong? I know for a fact that I should have results but I can't find anything

Here my query:

@Query("SELECT * FROM item WHERE myDate BETWEEN :dateStart AND :dateEnd")
suspend fun getBeersByDate(dateStart: String, dateEnd: String): List<Item>

Answer 1

what am I doing wrong? I know for a fact that I should have results but I can't find anything

Assuming that the dates are stored in the database the the format YYYY-MM-DD then what you are using should work (no need for single quotes as ROOM (well actually SQLite does when it binds the parameters passed))

But, only if the dateStart and dateEnd values differ and dateEnd is a later date than dateStart.

However it will not work if dateStart and dateEnd were the same e.g. 2020-05 and 2020-05. This is because if mydate, for example, is 2020-05-01 it is greater than 2020-05 (because the string contains additional characters).

• I assume that this is your issue that is you are using 2020-05 and 2020-05 as the parameters passed.

If you wanted to use 2020-05 with 2020-05 (as well as later such as 2020-06) then you either:

1. need to use 2020-05-01 (or 00 for the day) with 2020-05-31 (or up to 99 for the day) as it's a string search the invalid days doesn't matter.

2. or only use the relevant part of mydate for the search i.e. when using 2020-05 the first 7 characters and examples could be :-

@Query("SELECT * FROM item WHERE substr(mydate,1,length(:dateStart)) BETWEEN :dateStart AND :dateEnd;")

• note dateStart and dateEnd should be the same length, otherwise this is quite fleixible.

or

@Query("SELECT * FROM item WHERE strftime('%Y-%m',mydate) BETWEEN :dateStart AND :dateEnd;")

• the latter option requires that the date stored as a valid/recognised date as per https://sqlite.org/lang_datefunc.html (YYYY-MM-DD is valid/recognised).
• this is less flexible as the parameters must be in YYYY-MM format (but more flexible in regards to being able to cope the data being stored in any of the recognised formats).

If the above is not your issue then it is probably either due to coding issues that are not part of the question or that the data in the database is not stored in a compatible format (noting that unless you use datetime functions then SQLite does not consider stored values as dates, it is just data).

In either case, your question as it stands, does not include sufficient information to be able to say exactly what is wrong and you may therefore wish to update your question (perhaps to include a snapshot of the stored data (Android Studio's Database Inspector may be of use)).