Skip to content

SQL Changing Data Type

3 messages · GL, Dennis Murphy, Gabor Grothendieck

Original
GL
# 
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:
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
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
+     left join "df.possible.combos" using (Hour, Date)')
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
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.
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:
Gabor Grothendieck
#
On Fri, Jun 24, 2011 at 4:12 PM, GL <pflugg at shands.ufl.edu> wrote:
Please read the last line to every message to r-help and provide this
in reproducible form as requested.  Use dput to display all inputs and
showing all code. Also ensure you are using the latest version of
sqldf which is 0.4-1.