sqlite select with condition on date

SqlDatabaseSqliteDate

Sql Problem Overview


I have an sqlite table with Date of Birth. I would like to execute a query to select those records where the age is more than 30. I have tried the following but it doesn't work:

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

Sql Solutions


Solution 1 - Sql

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');

If you are using Sqlite V3.7.12 or greater

Dont use date(dateofbirth) just use dateofbirth. So your query would look like this:

select * from customer where dateofbirth < date('now','-30 years');

Solution 2 - Sql

Using the magic docs at the sqlite website:

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

Solution 3 - Sql

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionThunderView Question on Stackoverflow
Solution 1 - SqlThunderView Answer on Stackoverflow
Solution 2 - SqlMy Other MeView Answer on Stackoverflow
Solution 3 - SqlYadaView Answer on Stackoverflow