sqldf Date problem

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

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 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.

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.