CODEWITHSUNDEEP
sqldatabasesqlitedate
Thunder
Thunder

Reputation: 10986

sqlite select with condition on date

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'

Upvotes: 68

Views: 139216

Answers (5)

Blake
Blake

Reputation: 1

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.

Upvotes: 0

lucky andriana
lucky andriana

Reputation: 1

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

Upvotes: 0

Thunder
Thunder

Reputation: 10986

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

Upvotes: 105

My Other Me
My Other Me

Reputation: 5117

Using the magic docs at the sqlite website:

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

Upvotes: 26

Yada
Yada

Reputation: 31225

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.

Upvotes: 7

Related Questions