[R] sqldf Date problem

jim holtman jholtman at gmail.com
Sun Nov 4 03:13:04 CET 2012


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.




More information about the R-help mailing list