CODEWITHSUNDEEP
sqlr
dwcecil
dwcecil

Reputation: 31

Date criteria in sql query, in R

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)

This returns:

"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:

TRUE
FALSE

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.

Upvotes: 0

Views: 3332

Answers (1)

billcam
billcam

Reputation: 371

I just had this issue and solved it the following way. Access requires dates to be surrounded by the '#' symbol. The following query should work:

qry <- sqlQuery(channel, "select DATE, OTHERFIELD from DB where DATE >= #2000-01-01#")

Upvotes: 2

Related Questions