How can I filter on date values?

Using RODBC, I would like to query a table and return everyone who is 80 years old or older.

The query I use (within my R script) is:

sqlQuery(con, "SELECT *
              FROM People
              WHERE BirthDT >= '1941-02-11'")

The BirthDT field looks like this:

 [1,] "1937-07-12"
 [2,] "1939-04-20"
 [3,] "1938-11-10"
 [4,] "1987-10-24"
 [5,] "1991-07-04"
 [6,] "1984-03-24"
 [7,] "1986-03-14"
 [8,] "1937-08-18"
 [9,] "1938-09-28"
[10,] "1934-08-23"

I have also tried this (different date format specified):

sqlQuery(con, "SELECT *
              FROM People
              WHERE BirthDT >= '11/02/1941'")

As before, it doesn't filter as expected.

I have tried some variations of this query, including an explicit date cast/conversion, yet nothing seems to work.

Where am I going wrong?

Solution

80 years old or older.

This means that they were born earlier than current-date - 80 years. Your comparison is in the wrong direction:

sqlQuery(con, "SELECT *
              FROM People
              WHERE BirthDT <= '1941-02-11'")