R help - Nov 2007 - Query an Access database based on a date attribute (Tudor Bodea)

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.  

Harold Baize, PhD
Butte County Department of Behavioral Health
Chico, CA  USA

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

On Tue, 13 Nov 2007, Baize, Harold wrote:
> 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.
You could have asked RODBC: sqlTypeInfo() tells you what quote pairs to 
use with each type for the current DBMS.

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595