Select if date is older than 30 days. What to do if format is wrong?

I want to select all entries where the column “date” is older than 30 days. The problem is, that I dont have the date saved in a supported format.

My date is saved as ‘DD.MM.YYYY hh:mm:ss’. Is there a way to get such a selection within sqlite without changing the give database?

Or is my only or better option to select all rows and just parse it in code to get the desired rows?

Answer

I guess you can convert the strings to yyyy-mm-dd then compare with an identically format date, like so:

WHERE substr(datestr, 7, 4) || substr(datestr, 4, 2) || substr(datestr, 1, 2) >= strftime('%Y%m%d', 'now', '-30 days')