date system on SQL

Question

i just code some query which the database contain data "id and date"

003 18/01/1994
004 30/10/2007
005 31/08/1991

and i run query

select id from table where  (date >= #31/01/2017#)

the result showing only id 005 and if i run

select id from table where  (date <= #31/01/2017#)

the result is id 003 and 004 which is i think the correct query why did the id 5 did not show up instead show up if the operator >= ? can any one help me? what is wrong?

Answer 1

Do you really expect the date August 31, 1991 to appear as being later than January 31, 2017? The issue is that you are not really comparing dates numerically, you are comparing strings. You should be storing your date information in the form yyyy-mm-dd, something like this:

003 1994/01/18
004 2007/10/30
005 1991/08/31

Now, if you run your first query:

select id from table where date >= #2017/01/31#

you will get back empty set, and if you run the second query

select id from table where date <= #2017/01/31#

you should get back all records.