sqlite select with condition on date

Question

I have an sqlite table with Date of Birth.I would like to write a query to select those records where the age is more then 30 . I have tried following but it doesnot work.

select * from mytable where dob > '1/Jan/1980'
select * from mytable where dob > '1980-01-01'

Answer 1

Try writing using the date format 'YYYY-MM-DD'

select * from mytable where dob > '1980-01-01'

A more programic way would be something like this:

select * from mytable where datediff(dob, now()) > 30

You'll Need to find specific syntax for sqlite.

Answer 2

Some thing like this could be used:

select dateofbirth from customer Where DateofBirth  BETWEEN date('1004-01-01') AND date('1980-12-31');  
select dateofbirth from customer where date(dateofbirth)>date('1980-12-01');
select * from customer where date(dateofbirth) < date('now','-30 years');

Answer 3

Using the magic docs at the sqlite website:

select * from mytable where dob < date('now','-30 years');