Format date in Datatable output

Question

library(DT)
seq_dates <- data.frame(dates = as.Date("2017-01-01") + 1:6 * 100)
datatable(seq_dates) %>% formatDate(1, "toDateString")

I get a datatable in viewer pane displaying dates in following format "Mon May 22 2017".

Q - How can I format date column as "MM-YY"

If I do,

dplyr::mutate(seq_dates, dates = format(dates, format = "%b-%Y")) %>%
  datatable()

I get the required date format, but in this second case column sorting doesn't work (sorting is done on alphabets rather than dates.)

P.S - I'm implementing this on shiny.

Answer 1

Hi in these cases do I think the best solution is to add a dummy column with the dates in orginal format and have the dates column being sorted according to the values in the DUMMY column. This is in Datatable quite easily done. Example code below.

seq_dates <- data.frame(dates = as.Date("2017-01-01") + 1:6 * 100)
datatable(seq_dates %>% mutate(DUMMY = dates,dates = format(dates, format = "%b-%Y")),
          options = list(
            columnDefs = list(
              list(targets = 1,orderData = 2),
              list(targets = 2, visible = FALSE)
             )
           ))

Answer 2

For what it's worth (and using formatDate), the best that I can do is as follows:

datatable(seq_dates) %>% 
  formatDate(
    columns = 1, 
    method =  "toLocaleDateString", 
    params = list(
      'en-US', 
      list(
        year = 'numeric', 
        month = 'numeric')
      )
    )

And this yields date values like 4/2017 and 10/2017.

I've tried to find these parameter options (in github and the original datatables documentation) but to no avail. The only example in DT uses the parameters of short, long and numeric.