Sldf command returns negative value for date
On Thu, Aug 14, 2014 at 3:47 PM, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
Hi All!
I am trying to increment date column of data frame so as to merge it with
another data frame using sqldf:
my query is :
merge<-sqldf("select m.* ,e.* from mdata as m left join event as e on
date(m.Datest,'+1 day')=e.Start")
The query returns null for all columns related to event table.
When I investigated further with query :
sqldf("select date(Datest,'+1 day')") from eventflight;")
gives me -ve valued dates like : -4671-02-15
However this works:
sqldf("select date(('2009-05-01'),'+1')")
Dataframes are as follows:
mdata :
LOS Arrivals BookRange Datest
1 1283 0-4 2009-05-01
1 1650 0-4 2009-05-08
1 1302 5-9 2009-05-15
event:
Event.Name Event.location Start End
Birthday Texas (US) 2009-05-02 2009-05-03
Anni Texas (US) 2009-05-09 2009-01-11
What am I doing wrong?
This is a FAQ. See #4 here: http://sqldf.googlecode.com . The SQLite date function assumes its argument is a timestring but R "Date" class variables are transferred to SQLite as days since 1970-01-01 so just add 1. sqldf("select * from mdata as m left join event on Datest+1 = Start")
Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com