SQLite search for dates past a year old

My brain is not working today :confused:

I have a table called ITEM and when a product is serviced the date is stored as SQLDATETIME as so 2017-11-02 10:39:00 in a field called PMI

I’m trying to figure a way to query the Table and find all the PMI dates that are a year old or older.

Just figured I would ask first because maybe someone did this type of query before

Xojo 2018 1.1 using the built in SQLite

A year ago would be:

datetime('now', '-1 years')

so a generic search would be:

select * from my_table where my_date < datetime('now', '-1 years')

@Scott

Thank you very much. I will go try some queries out and let you know :smiley:

I don’t have a db handy but you may need to wrap your date in datetime() too:

select * from my_table where datetime(my_date) < datetime('now', '-1 years')

Yes had to wrap date in datetime()

Thanks again