[R] sqldf Date problem

[jim holtman]

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