Ex13 - datetime


#1

Hi,
Got a question about the datetime used in ex13.
Write a query that can find all the names of pets and their owners bought after 2004 (in my case after 2017).
This is my person_pet database:

select * from person_pet;
person_id   pet_id      purchased_on
----------  ----------  ------------
0           0           21-05-2018
0           1           03-03-2016
1           2           05-10-2008
1           3           01-01-2010
4           5           23-12-2015
6           4           23-12-2016

I tried several attempts to get pet_id WHERE purchased_on is > ‘2017’ without the correct output.

For example the SELECT used in the video:
SELECT pet_id FROM person_pet WHERE purchased_on > '2017';
pet_id
----------
0
1
2
3
5
4

This is certainly not correct, as only the 1st pet was bought after 2017.
Also tried WHERE purchased_on > date(‘2017’); WHERE purchased_on > WHERE purchased_on > ‘01-01-2017’;
Any of that give me the correct output.

Anyway this was an excellent exercise to practice all the skills learned.
Thanks a lot


#2

Try ‘01-01-2017’, and also look at the date functions in SQLite:

https://www.sqlite.org/lang_datefunc.html

You might need to use some of those to get it to become a date for comparison, but IIRC the string should just work.