I'm using sqlite3 in my python local application.
I'm getting one of the weirdest bugs ever, I have a column named date_bought, with date values from 28/08/2023 to 30/08/2023.
Executing this SQL query retrieves the data wanted:
select *
from sold
where date_added between '29/08/2023' and '31/08/2023'
and returns this result :
123123|souris|70.0|150.0|150.0|3|450|450|29/08/2023 14:48:53|29/08/2023 14:57:16
123123|souris|70.0|150.0|150.0|2|300|300|29/08/2023 14:48:53|29/08/2023 14:57:36
123123|souris|100.0|150.0|150.0|3|450|450|29/08/2023 14:56:20|29/08/2023 14:57:43
The problem now is that if I execute :
select *
from sold
where date_added between '29/08/2023' and '01/09/2023'
There is no result, hence changing to 31/09/2023 and any day of 09 gives the same results as if it was 08, it feels like it compares only the days not the months and the years and i can not figure why is this happening ?
Thank you for you time. I wish you a good day.
SQLite has no real date column. It stores strings. If you want to store dates and times in strings, you'll use this format: YYYY-MM-DD HH:MM:SS.SSS. Here it doesn't really matter whether the DBMS looks at this as a string or a datetime, because '2023-08-29' comes before '2023-09-01', as the first six characters in the string are equal and for the seventh character '9' is greater than '8'.
If you store dates in a format dd/mm/yyyy, though, you get two strings '29/08/2023' and '01/09/2023', and '01/09/2023' comes before '29/08/2023', because the first character '0' is already less than '2'.
So, I guess your problem is that you stored dates in an unfortunate format. You'll have to work with substrings to compare two entries. Or better, you update all your values to have the appropriate date format yyyy-mm-dd in the first place.
update sold
set date_added = substr(date_added, 7, 4) || '-' ||
substr(date_added, 4, 2) || '-' ||
substr(date_added, 1, 2) ||
substr(date_added, 11);
select *
from sold
where date_added between '2023-08-29' and '2023-09-01';