0

I have a column as dt in my Dataframe as below

dt

01-Jan-2017 12:00:00
12/01/2017   01:15:00
13-Sep-97    21:00:00
20 August 2017 22:00:00
12/19/17   01:15:00
2/4/2017    05:18:00

How to get R date time format in R_dt column

   dt                         R_dt
01-Jan-2017 12:00:00         2017-01-01 12:00:00
12/01/2017   01:15:00        2017-12-01 01:15:00
13-Sep-97    21:00:00        1997-09-13 21:00:00
20 August 2017 22:00:00      2017-08-20 22:00:00
12/19/17   01:15:00          2017-12-19 01:15:00
2/4/2017    05:18:00         2017-02-04 05:18:00

I have tried using - strftime(mydf$date,"%d/%m/%Y")- which throws error.

Roman Luštrik
  • 69,533
  • 24
  • 154
  • 197
snr999
  • 105
  • 1
  • 1
  • 7
  • You could try the `lubridate::guess_formats`. Another option is [this](http://stackoverflow.com/questions/25463523/convert-variable-with-mixed-date-formats-to-one-format-in-r). – Roman Luštrik May 22 '17 at 11:13

1 Answers1

2

You have multiple formats, you need a function that automagically tries multiple formats. anytime() from my anytime package is one such function:

R> dvec <- c("01-Jan-2017 12:00:00", "12/01/2017 01:15:00", "13-Sep-97 21:00:00", 
=            "20 August 2017 22:00:00", "12/19/17 01:15:00", "2/4/2017 05:18:00")
R> dvec
[1] "01-Jan-2017 12:00:00"    "12/01/2017 01:15:00"    
[3] "13-Sep-97 21:00:00"      "20 August 2017 22:00:00"
[5] "12/19/17 01:15:00"       "2/4/2017 05:18:00"      
R> anytime(dvec)
[1] "2017-01-01 12:00:00 CST" "2017-12-01 01:15:00 CST"
[3] NA                        "2017-08-20 22:00:00 CDT"
[5] NA                        NA                       
R>

You see that three fail: two use a two-digit year (format %y) which we don't support by default -- but for which we offer the ability to add formats via addFormat() so one can address this:

R> addFormats(c("%d-%b-%y %H:%M:%S", "%m/%d/%y %H:%M:%S"))
R> anytime(dvec)
[1] "2017-01-01 12:00:00 CST" "2017-12-01 01:15:00 CST"
[3] "2097-09-13 21:00:00 CDT" "2017-08-20 22:00:00 CDT"
[5] "2017-12-19 01:15:00 CST" NA                       
R>

The last one fails because of single digit month and day -- and that is not parsed by the Boost code we use so I have no (easy) fix for it.

However, for reasons related to testing and comparison, we now also allow use of R's internal parser (via my package RApiDatetime) so you possible can post-process those:

R> anytime(dvec[6], useR=TRUE)
[1] "2017-02-04 05:18:00 CST"
R>
Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
  • Thanks @Dirk , How to add format if the day has single digit as "12/1/2017 03:00:00" – snr999 May 22 '17 at 11:54
  • Please re-read the last sentence of my original answer: You cannot. But I added a possibility using R's parser--see the edited answer. – Dirk Eddelbuettel May 22 '17 at 11:54
  • That will change with the next release at the end of the month / early next month which will cover a date like "12/1/2017" (and a datetime, of course). – Dirk Eddelbuettel Aug 07 '19 at 23:12