[R] sqldf Date problem

[Andreas Recktenwald]

Zitat von jim holtman <jholtman at gmail.com>:

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


Thanks for your quick response Jim,

you are right the entries in my "Date" column are characters (my fault  
not to mention this in my first post).


Now i know the reasons for my problem and can solve it.