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.

Unwind

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

Eggcup

Nice workaround. I was hoping for a solution with formatDate(). If there is no more direct solution in a few days, I will validate yours. – Action

Since I (OP) needed the MM-YY format, I converted the date col as a factor with proper levels - that did the trick! – Unwind

@Unwind cool that you found your own solution, but as my solution allowed for every kind of date format, so I don't see any need for you to down vote the answer. – Eggcup

@Unwind I mean to get what you want just change to format = "%m-%y" – Eggcup

By today, the column-enumeration starts with 0. – Arawak

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.

Statvolt

The params refer to the parameters the JavaScript method takes, in this case the parameters for toLocaleDateString. The parameters for toLocalDateString can be found on MDN, and in the docs for Intl.DateTimeFormat. – Callimachus

Answer 3

Converting "%b-%y" "dates" to date format is not an easy thing as I could see...

If you're not too attached to displaying "%b-%y" format, the easy way is to use "%Y-%m" or "%y-%m" format and the filter will work just fine :

library(DT)

seq_dates <- as.data.frame(seq(Sys.Date() - 100, Sys.Date(), by = "m"))
seq_dates <- format(seq_dates, format = "%y-%m")

datatable(seq_dates)

#resulting in

#1        2017-02
#2        2017-03
#3        2017-04
#4        2017-05

#or

#1        17-02
#2        17-03
#3        17-04
#4        17-05

Drexler

Answer 4

There is a render method that you can use:

datatable( ...
  options = list(..
     columnDefs = list(..
        list(targets = c(1), render = JS(
                             "function(data, type, row, meta) {",                              
                              "return type === 'display' ? new Date(data).toLocaleString() : data;"))))
                        

Sophronia

Answer 5

You can achieve this using lubridate's yearmon and converting the column to character.

library(DT)
library(lubridate)

seq_dates <- data.frame(dates = as.Date("2017-01-01") + 1:6 * 100)
seq_dates$YEARMON <- as.character(as.yearmon(seq_dates$dates))
datatable(seq_dates)

Subminiaturize