64

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'
isync
  • 537
  • 6
  • 15
Thunder
  • 10,366
  • 25
  • 84
  • 114

5 Answers5

99

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');
JKennedy
  • 18,150
  • 17
  • 114
  • 198
Thunder
  • 10,366
  • 25
  • 84
  • 114
  • 26
    All those who come here from google: now it doesn't work anymore with sqlite v3.7.12; don't use `date(dateofbirth)`, just `dateofbirth` would be good. – wecing Jan 12 '13 at 22:16
  • 2
    Just need to be careful with one thing: ```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**. – mojmir.novak May 20 '18 at 14:49
25

Using the magic docs at the sqlite website:

select * from mytable where dob < date('now','-30 years');
My Other Me
  • 5,007
  • 6
  • 41
  • 48
5

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.

Yada
  • 30,349
  • 24
  • 103
  • 144
  • 4
    Back in 2010 `select * from mytable where dob > '1980-01-01'` might not work, but currently this command works perfectly on `sqlite3`. – Shane Dec 17 '13 at 09:56
  • Sqlite says *`Error (1): no such function: now`* and *`Error (1): no such function: datediff`*. – jww Oct 31 '19 at 05:15
0

select * from mytable where date(dob) > date('1980-01-10')

lucky andriana
  • 1
0

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.

Blake
  • 1
  • 1