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'
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');
date(dateofbirth), just dateofbirth would be good.
date(field) > date('2018-05-20') and field > '2018-05-20' returns different results for me so using function date() is the only way how it works correctly for me if the field is not Date but DateTime.Using the magic docs at the sqlite website:
select * from mytable where dob < date('now','-30 years');
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.
select * from mytable where date(dob) > date('1980-01-10')
QLite3 has some cool new date functions. Per the docs site you can use date(), time(), datetime(), julianday(), unixepoch(), or strftime() depending on how your column data is formatted.
If you use strftime(), like my suggestion below, then you have to make sure that your column data is formatted the same way as your strftime string.
You would probably want something like:
SELECT * FROM mytable WHERE dob < strftime("%m/%d/%Y", 'now', '-30 year');
Note that you might have to change the format string here to match your own.
And here's some code that I use personally to give you a better idea of how powerful it is. It lets me get all the orders from the previous 3 months, not including this month.
SELECT * FROM orders WHERE SHIPPEDDATE > strftime('%m/%d/%Y', 'now', 'start of month', '-3 month');
The modifiers are very powerful with sqlite. The first string inside strftime() is the format, the 2nd string is when you want the date to start. 'Start of month' puts the day to 1, and '-3 month' goes back 3 months. So if I ran that today (08/03/2022), the date it uses would be 05/01/2022.
