+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+--- Thread: sqlite select with condition on date (/Thread-sqlite-select-with-condition-on-date)
sqlite select with condition on date - korneyc - 07-20-2023
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'
RE: sqlite select with condition on date - fenske113 - 07-20-2023
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.
RE: sqlite select with condition on date - dirtinesses46044 - 07-20-2023
Using the magic docs at [the sqlite website][1]:
select * from mytable where dob < date('now','-30 years');
[1]:
[To see links please register here]
RE: sqlite select with condition on date - annikakjzowcul - 07-20-2023
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');
RE: sqlite select with condition on date - anonyms568308 - 07-20-2023
select * from mytable where date(dob) > date('1980-01-10')
RE: sqlite select with condition on date - dantzler828 - 07-20-2023
QLite3 has some cool new date functions. Per the [docs site](
[To see links please register here]
) 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.