[R] sqlQuery with date attribute

From: Abderrazzak MANY <amany_at_messel.emse.fr>
Date: Tue, 12 Aug 2008 16:49:21 +0200


Hello R users,

I would like to use an integer and a date as attributes in sqlQuery, and these arguments
are defined in my function.
I guess this is clearer comparing to my first post.

Here is my function:

GetReturn<-function(code,date)
{
db<-"C:/Test.mdb"
channel<-odbcConnectAccess(db)
ssql<-paste("select * from tblCalendarDate Where CalendarID =",code,"and DateRebal >=",date)

print(ssql) # so as i can see what ssql contains mydata<-sqlQuery(channel,ssql)
mydata
}
This is the content of my table tblCalendarDate:

CalendarID	DateRebal
1	29/09/2006
1	10/10/2006
1	20/10/2006
1	31/10/2006
1	10/11/2006
1	20/11/2006
1	30/11/2006
1	08/12/2006
1	20/12/2006
1	29/12/2006
1	10/01/2007
1	19/01/2007
1	31/01/2007
1	09/02/2007
1	20/02/2007
1	28/02/2007
1	09/03/2007
1	20/03/2007
1	30/03/2007
2	31/05/2006
2	30/06/2006
2	31/07/2006
2	31/08/2006
2	29/09/2006
2	31/10/2006
2	30/11/2006
2	29/12/2006
2	31/01/2007
2	28/02/2007
2	30/03/2007
2	30/04/2007
2	31/05/2007
2	29/06/2007
2	31/07/2007
2	31/08/2007
2	28/09/2007
2	31/10/2007
2	30/11/2007
2	28/12/2007

Actually, the channel is open but the query on the table did not perform the query correctly, here is the result of the function when i run GetReturn(1,"2007-03-01") for example:
> GetReturn(1,"2007-03-01")
[1] "select * from tblCalendarDate Where CalendarID = 1 and DateRebal  >= 2007-03-01"

    CalendarID DateRebal

1           1 2006-09-29
2           1 2006-10-10
3           1 2006-10-20
4           1 2006-10-31
5           1 2006-11-10
6           1 2006-11-20
7           1 2006-11-30
8           1 2006-12-08
9           1 2006-12-20
10          1 2006-12-29
11          1 2007-01-10
12          1 2007-01-19
13          1 2007-01-31
14          1 2007-02-09
15          1 2007-02-20
16          1 2007-02-28
17          1 2007-03-09
18          1 2007-03-20
19          1 2007-03-30

The command paste worked for the CalendarID argument but not for the second argument DateRebal, I still
have the records where the date is < 2007-03-01.

Thank you for helping.

A.Many



R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Tue 12 Aug 2008 - 14:53:44 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Wed 13 Aug 2008 - 08:33:38 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive