On Thu, Aug 14, 2014 at 3:47 PM, Sneha Bishnoi <[email protected]> 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 ______________________________________________ [email protected] 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.
