[R] SQL Changing Data Type

[Dennis Murphy]

Hi:

str() probably would have been more informative than summary(), but if
the date is a factor, perhaps this will help:

as.Date(as.character(Date), format = '%Y-%m-%d')

Including the format argument is usually safer than letting the
function try to figure it out on its own. Neglecting to include it may
well be the source of the error message.

You might also look into the POSIX classes if you want to combine date
and time. Start with ?DateTimeClasses to learn more.

HTH,
Dennis

On Fri, Jun 24, 2011 at 1:12 PM, GL <pflugg at shands.ufl.edu> wrote:
> Passing in two dates to a sql statement (sqldf). Is returning a factor. Tried
> setting back to a Date via as.Date, but get an error the error: character
> string is not in a standard unambiguous format. Any thoughts appreciated.
>
> Code/Results listed below:
>
>> summary(df.possible.combos)
>      Date                 Hour
>  Min.   :2011-03-01   Min.   : 0.00
>  1st Qu.:2011-03-23   1st Qu.: 5.75
>  Median :2011-04-14   Median :11.50
>  Mean   :2011-04-14   Mean   :11.50
>  3rd Qu.:2011-05-06   3rd Qu.:17.25
>  Max.   :2011-05-31   Max.   :23.00
>> summary(df.aggregate)
>      Date                 Hour           x
>  Min.   :2011-03-01   16     : 82   Min.   : 1.000
>  1st Qu.:2011-03-22   17     : 82   1st Qu.: 1.000
>  Median :2011-04-13   18     : 82   Median : 2.000
>  Mean   :2011-04-14   19     : 79   Mean   : 4.195
>  3rd Qu.:2011-05-07   20     : 76   3rd Qu.: 7.000
>  Max.   :2011-05-31   7      : 75   Max.   :20.000
>                      (Other):377
>> #merge raw data and all possible combinations
>>   df.final <- sqldf('select Date, Hour, x as RoomsInUse from
>> "df.aggregate"
> +     left join "df.possible.combos" using (Hour, Date)')
>> summary(df.final)
>      Date          Hour       RoomsInUse
>  15069.0: 16   16     : 82   Min.   : 1.000
>  15114.0: 16   17     : 82   1st Qu.: 1.000
>  15063.0: 15   18     : 82   Median : 2.000
>  15082.0: 15   19     : 79   Mean   : 4.195
>  15125.0: 15   20     : 76   3rd Qu.: 7.000
>  15044.0: 14   7      : 75   Max.   :20.000
>  (Other):762   (Other):377
>> thedate <- as.Date(df.final$Date)
> Error in charToDate(x) :
>  character string is not in a standard unambiguous format
>>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/SQL-Changing-Data-Type-tp3623508p3623508.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> 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.
>