[R] Converting Multiple Columns of Data Frame to Date

[John Bodley]

Hi,

I have a data frame which has a subset of columns containing character
representations of dates. The data frame is obtained via the
DBI::fetch(rs, ...) method. I would like to convert all the columns
which were originally dates in the database to R dates. I can obtain a
logical index of columns which are dates via

> idx <- dbColumnInfo(rs)$type == "DATE".

I'm having trouble converting these columns to R Date objects, A mock
example is:

> df <- data.frame(blah = c(1, 2), start = c("01-Jun-09", "02-Jun-09"), end = c("01-Jul-09", "02-Jul-09"))
> idx <- c(FALSE, TRUE, TRUE)
> tmp <- as.Date(as.matrix(df[, idx]), "%d-%b-%y")
> print(tmp)
[1] "2009-06-01" "2009-06-02" "2009-07-01" "2009-07-02"

which seems correct, however when I assign the values back to the
original data frame:

> df[, idx] <- tmp
> print(df)
  blah start   end
1    1 14396 14426
2    2 14397 14427

the associated dates are converted to numeric. Note that this approach
only seems to fail if idx contains two or more columns, i.e., using
the original definition of df and selecting one date column

> idx <- c(FALSE, TRUE, FALSE)
> df[, idx] <- as.Date(as.matrix(df[, idx]), "%d-%b-%y")
> print(df)
   blah      start       end
1    1 2009-06-01 01-Jul-09
2    2 2009-06-02 02-Jul-09

Returns the correct format for df$start. I don't know a-priori which
columns are SQL dates, so I need to work will column indices rather
than:

> df <- transform(df, start = as.Date(start, "%d-%b-%y")

Any ideas?

Thanks
-John