[Solved] Get current week data in sqlite3?

Question

Your problem is that you are formatting your dates in %d-%m-%Y format, which will not work in a BETWEEN expression because it uses string comparison; for example the date 10-01-2999 would be between 09-01-2022 and 11-01-2022. You need to format them as %Y-%m-%d for BETWEEN to work correctly.

If the dates in your table are not formatted as %Y-%m-%d then you will need to update the table to put them into that format, using something like:

UPDATE cars_buying_checks
SET paymentdate = SUBSTR(paymentdate, 7, 4) || '-' || SUBSTR(paymentdate, 4, 2) || '-' || SUBSTR(paymentdate, 1, 2)

Accepted Answer

Your problem is that you are formatting your dates in %d-%m-%Y format, which will not work in a BETWEEN expression because it uses string comparison; for example the date 10-01-2999 would be between 09-01-2022 and 11-01-2022. You need to format them as %Y-%m-%d for BETWEEN to work correctly.

If the dates in your table are not formatted as %Y-%m-%d then you will need to update the table to put them into that format, using something like:

UPDATE cars_buying_checks
SET paymentdate = SUBSTR(paymentdate, 7, 4) || '-' || SUBSTR(paymentdate, 4, 2) || '-' || SUBSTR(paymentdate, 1, 2)