Неправильное сравнение дат в SQLite

В бд есть столбец DATE где дата хранится в формате dd.mm.yyyy. Пытаюсь таким запросом: DELETE FROM PRODUCTS WHERE DATE <= date('now','-30 day') удалить все строки старше 30 дней, но удаляются все строки. Что не так?

Ответ

В SQLite нет "настоящего" типа данных DATE - данные типа DATE/TIME могут храниться в одном из следующих видов:
строк/text (обычно используют ISO8601: "YYYY-MM-DD HH:MM:SS.SSS") целых чисел (UNIX epoch - число секунд прошедших с 1970-01-01 00:00:00 UTC) вещественных чисел (as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar).
Соответственно, при использовании строк при сравнении будет использоваться лексикографическое сравнение. Например, "2" > "1999999", т.к. первый символ первой строки (двойка) больше единицы, первого символа во второй строке.
Это одна из причин почему в SQLite надо хранить дату/время только в одном из следующих форматов
YYYY-MM-DD YYYY-MM-DD HH:MM YYYY-MM-DD HH:MM:SS YYYY-MM-DD HH:MM:SS.SSS YYYY-MM-DDTHH:MM YYYY-MM-DDTHH:MM:SS YYYY-MM-DDTHH:MM:SS.SSS HH:MM HH:MM:SS HH:MM:SS.SSS now DDDDDDDDDD
Вывод: если вам нужен другой формат даты, то форматируйте его только для вывода, но не для хранения.