Select entries between two dates with different years-CodePudding

I have the following table structure:

id val1 val2 val3 date
1   1    2    3   14.12.2021
2   2    3    5   17.12.2021
3   4    6    8   18.12.2021
.   .    .    .   .
.   .    .    .   .
.   .    .    .   .
9   3    4    5   04.01.2022

so far i use the following command:

SELECT * FROM table WHERE `date` >= '13.12.2021' and `date` <= '24.12.2021'

but if i want to go into the new year, the command returns me no value

SELECT * FROM table WHERE `date` >= '13.12.2021' and `date` <= '04.01.2022'

Does anyone have an idea how I have to modify the command so that it works?

CodePudding user response：

Because you store date in wrong format you should transform it in where clause:

select * 
from test
where substr(date,7)||'-'||substr(date,4,2)||'-'||substr(date,1,2) between '2021-12-13' and '2022-01-04';

SQLite sandbox here

But better way is - store dates in proper format YYYY-MM-DD