[R] Query an Access database based on a date attribute (Tudor Bodea)

Emmanuel Charpentier charpent at bacbuc.dyndns.org
Tue Nov 13 19:14:30 CET 2007


Baize, Harold a écrit :
> Tudor Bodea asked:
> 
>> In this context, I try to get all the records for which market is atl-bos,
>> competitor is delta and dd is 2007-11-20 (first record above). To do this I used
> 
>>> # channel <- odbcConnectAccess("test.mdb")
>>> res <- sqlQuery(channel, "select * from test_table where market = 'atl-bos'
>>> and competitor = 'delta' and dd = 2007-11-20")
> 
>> Does any of you know what it is going on?
> 
> Try this:
> 
> res <- sqlQuery(channel, "select * from test_table 
>    where market = 'atl-bos'and competitor = 'delta' and dd = {d '2007-11-20'}")
> 
> Microsoft variant of SQL has an odd date syntax. I tried a dozen things before 
> I found what worked.  


FWIW : This syntax is *not* MS-Access-specific, but a (relatively)
common ODBC syntactic kludge (syntactic vinegar ?). ISTR that I had to
use something like this with older versions of unix-ODBC and PostgreSQL
ODBC driver.

HTH,

					Emmanuel Charpentier



More information about the R-help mailing list