android - Finding all the entries in a table having the column containing a substring

Question

I am using Room database in android(SQL) and I have a table that contains date as a column stored as a string of format 2000-01-31. I want to get all the entries in the table according to their respective month. I was thinking of using

SELECT * FROM `Transactions` WHERE Date LIKE '%01%

But in this way, I will have to write separate queries for each month. I want to get the entries month-wise and show them in my android application.

My table looks like this...

标签： androidsqldatabaseandroid-room

Answer 1

这回答了最初提出的问题。

不要对日期使用字符串操作。如果您想要特定年份的特定月份，请使用：

where date >= '2021-01-01' and date < '2021-02-01'

如果你想要所有的一月，那么像这样：

where month(date) = 1

month()是用于此目的的常用函数，但它可能因您的数据库而异。