I’m using sqflite in my Flutter application. In this example, date is the actual date(10.06.2021) and DATUM is the datafield in the DB.
var x = await db.rawQuery('SELECT COUNT (*) FROM Shisha_table WHERE YEAR('+date+') - YEAR(DATUM) = 1'); int count = Sqflite.firstIntValue(x); return count;
I keep getting this syntax error:
E/SQLiteLog(30657): (1) near “.2021”: syntax error in “SELECT COUNT (*) FROM Shisha_table WHERE (YEAR(10.06.2021) – YEAR(DATUM)) = 1”
Advertisement
Answer
There is no YEAR()
function in SQLite.
The logic of your code can be written by extracting the year of a date with the use of the function strftime()
:
SELECT COUNT(*) FROM Shisha_table WHERE strftime('%Y', CURRENT_DATE) - strftime('%Y', DATUM) <= 1
Or, if you want the rows from the same date in last year untill today:
SELECT COUNT(*) FROM Shisha_table WHERE DATUM >= date(CURRENT_DATE, '-1 year')