Re: [R] sqlQuery with date attribute
Thank you for your answer.
Actually, I've tried with this function where I added the # symbol
between the date:
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
}
GetReturn(1,"2007-01-10")
And it works when I run simply the command GetReturn(1,"2007-03-01")
"Samuel Bächler" <[EMAIL PROTECTED]> a écrit :
Hi Many
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
}
[snip]
This is the content of my table tblCalendarDate:
CalendarIDDateRebal
129/09/2006
110/10/2006
120/10/2006
131/10/2006
110/11/2006
120/11/2006
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:
Something with the formatting of the date goes wrong as I think. In
the table tblCalendarDate you have it
like *29/09/2006* but in your function you have it as
*"2007-03-01"*. Dig deeper by experimenting with
the dates format. You can experiment in Access itself to see what
kind of dates Access accepts.
s.
__
R-help@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.
__
R-help@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.
[R] sqlQuery with date attribute
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@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.
[R] [RODBC] sqlQuery with date attribute
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.
Here is my function:
GetReturn<-function(code,date)
{
channel<-odbcConnect("db","user1","password")
ssql<-paste("select * from TABLE Where PF_CODE =",code,"and PF_DATE=",date)
mydata<-sqlQuery(channel,ssql)
mydata
}
Actually, the channel is open but the query on the table did not work.
The command paste worked for the integer argument but not for the date.
Thank you for helping.
A.Many
__
R-help@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.
[R] [RODBC] date attribute in sqlQuery
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.
Here is my function:
GetReturn<-function(code,date)
{
channel<-odbcConnect("db","user1","password")
ssql<-paste("select * from TABLE Where PF_CODE =",code,"and PF_DATE=",date)
mydata<-sqlQuery(channel,ssql)
mydata
}
Actually, the channel is open but the query on the table did not work.
The command paste worked for the integer argument but not for the date.
Thank you for helping.
A.Many
__
R-help@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.
