Most likely your "Date" is either a character or a factor (you need to provide an 'str' of the dataframe). You are therefore most likely doing a character compare and that is the reason for your problem. You need to convert to a character string of the format YYYY-MM-DD to do the correct character comparison.
############## > x <- data.frame(Date = paste0('1/', 1:31, '/2011')) > str(x) 'data.frame': 31 obs. of 1 variable: $ Date: Factor w/ 31 levels "1/1/2011","1/10/2011",..: 1 12 23 26 27 28 29 30 31 2 ... > x Date 1 1/1/2011 2 1/2/2011 3 1/3/2011 4 1/4/2011 5 1/5/2011 6 1/6/2011 7 1/7/2011 8 1/8/2011 9 1/9/2011 10 1/10/2011 11 1/11/2011 12 1/12/2011 13 1/13/2011 14 1/14/2011 15 1/15/2011 16 1/16/2011 17 1/17/2011 18 1/18/2011 19 1/19/2011 20 1/20/2011 21 1/21/2011 22 1/22/2011 23 1/23/2011 24 1/24/2011 25 1/25/2011 26 1/26/2011 27 1/27/2011 28 1/28/2011 29 1/29/2011 30 1/30/2011 31 1/31/2011 > > require(sqldf) > # not correct because of character compares > sqldf('select * from x where Date > "1/13/2011" and Date < "1/25/2011"') Date 1 1/2/2011 2 1/14/2011 3 1/15/2011 4 1/16/2011 5 1/17/2011 6 1/18/2011 7 1/19/2011 8 1/20/2011 9 1/21/2011 10 1/22/2011 11 1/23/2011 12 1/24/2011 > # convert the date to YYYY/MM/DD for character compares > x$newDate <- as.character(as.Date(as.character(x$Date), format = "%m/%d/%Y")) > # now do the select > sqldf('select * from x where newDate between "2011-01-13" and "2011-01-25"') Date newDate 1 1/13/2011 2011-01-13 2 1/14/2011 2011-01-14 3 1/15/2011 2011-01-15 4 1/16/2011 2011-01-16 5 1/17/2011 2011-01-17 6 1/18/2011 2011-01-18 7 1/19/2011 2011-01-19 8 1/20/2011 2011-01-20 9 1/21/2011 2011-01-21 10 1/22/2011 2011-01-22 11 1/23/2011 2011-01-23 12 1/24/2011 2011-01-24 13 1/25/2011 2011-01-25 On Sat, Nov 3, 2012 at 4:22 PM, Andreas Recktenwald <a.recktenw...@mx.uni-saarland.de> wrote: > Dear R-help readers, > > i've created a database for quotes data (for 4 years; 2007 -- 2010) with the > sqldf package. This database contains a column "Date" in the format > mm/dd/yyyy. > > The table in the database is called "main.data" and the database itself > "Honda". I tried to get the Data just for certain period, say from > 01/01/2007 until 01/10/2007 with the following code: > > sqldf("select * from main.data where Date<='01/10/2007' and > Date>='01/01/2007'), > dbname="Honda") > > > I get the data for this period for every year(2007,2008,2009,2010) not only > for 2007. It seems that the year is "overlooked" and just looked for the > fitting days and months. > > Because I haven't really much experience with sql I decide to send my > problem to the list. > > Many thanks in advance. > > ______________________________________________ > 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. -- Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it. ______________________________________________ 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.