I'm having a problem refining an SQL query within R. I'm querying an Microsoft Access database. I'm not sure if this is an SQL problem or an R problem. I use RODBC to establish a connection, then I'm trying to limit a query by just getting results from 2000-1-1 onward.
When I query the entire field, I used str() to determine the exact date type so I could create a comparison variable. So, I got something like
wholeqry <- sqlQuery(channel, paste("select DATE, OTHERFIELD from DB", sep="")) (testdate <- wholeqry[1,1]) str(testdate)
"1984-11-29 EST" POSIXct[1:1], format: "1984-11-29"
Ok, so I format a variable so I can compare it to this:
start <- as.POSIXct(strptime("2000-01-01", format="%Y-%m-%d"), tz = "EST")
Then I check to see if they are indeed comparable with logic operators:
testdate < start start < testdate
The results, as expected:
Ok, now I feel like I'm ready to do my query. I use paste() to pass start as a variable in the sql statement:
qry <- sqlQuery(channel, paste("select DATE, OTHERFIELD from DB where DATE >=", start, sep=""))
But, when I check qry, it's the whole field minus two entries. The really confusing part to me is if I check the same entry in my new qry, I get the same date, and can compare it to my start variable, and still get the correct logic results. That is:
(testdate <- qry[1,1]) testdate < start start < testdate
Gives the results:
"1984-11-29 EST" TRUE FALSE
So, while two records were kicked out by the comparison, the rest weren't, even though if I manually compare them, they should be rejected. I considered that the format of the date field may not be uniform, as some entries have time zone EDT as opposed to EST, but if I check those entries against my start date, I still get the correct logic results.