Change Format of Date Column

Question

I need to turn one date format into another with RStudio, since for lubridate and other date related functions a standard unambiguous format is needed for further work. I've included a few examples and informations below:

Example-Dataset:

Function,HiredDate,FiredDate
Waitress,16-06-01 12:40:02,16-06-13 11:43:12
Chef,16-04-17 15:00:59,16-04-18 15:00:59

Current Date Format (POSIXlt) of HiredDate and FiredDate:

"%y-%m-%d %H:%M:%S"

What I want the Date Format of HireDate and FiredDate to be:

"%Y-%m-%d %H:%M:%S" / 2016-06-01 12:40:02

or

"%Y/%m/%d %H:%M:%S" / 2016/06/01 12:40:02

POSIXlt does not have a specific date format. When you print a POSIXlt vector, then a specific format is used to convert the time stamp into a character, which is then printed. You can control the format of the character with `format()`. — Stibu, Apr 13 '16 at 14:08
I have tried converting the following: 1. use ymd() from the lubricate package to convert my date to appropriate format ||||||||||||||||| 2. convert my column into a date.class but losing my time. ||||||||||||||||| 3. trying to use POSIXct to somehow get my date ||||||||||||||||| So basically, i haven't tried using something like strftime, it's very difficult to find any other examples. * — BrahminXy, Apr 13 '16 at 16:42

jenzopr · Accepted Answer · 2016-04-14T07:54:46.187

In principle, you can convert date and time for example using the strftime function:

    d <- "2016-06-01 12:40:02"
    strftime(d, format="%Y/%m/%d %H:%M:%S")
    [1] "2016/06/01 12:40:02"

In your case, the year is causing trouble:

    d <- "16-06-01 12:40:02"
    strftime(d, format="%Y/%m/%d %H:%M:%S")
    [1] "0016/06/01 12:40:02"

As Dave2e suggested, the two digit year can be read by %y:

    strftime(d, format="%y/%m/%d %H:%M:%S")
    [1] "16/06/01 12:40:02"

Assuming that your data comes from the 20st and 21st century, you can paste a 19 or 20 in front of the HireDate and FireDate:

    current <- 16
    prefixHire <- ifelse(substr(data$HireDate, 1, 2)<=currentYear,20,19)
    prefixFire <- ifelse(substr(data$FireDate, 1, 2)<=currentYear,20,19)
    data$HireDate = paste(prefixHire, data$HireDate, sep="")
    data$FireDate = paste(prefixFire, data$FireDate, sep="")

The code generates a prefix by assuming that any date from a year greater than the current ('16) is actually from the 20th century. The prefix is then pasted to HireDate and FireDate.

That worked very well, that ifelse construct with substring is something i have to keep in mind for future manipulations, that seems very handy. Thank you, have a nice day. — BrahminXy, Apr 13 '16 at 16:56
Use a %y for reading 2 digit years and %Y for 4 digit years. When to use %y - Years without century (00–99). On input, values 00 to 68 are prefixed by 20 and 69 to 99 by 19 — Dave2e, Apr 13 '16 at 20:14

Change Format of Date Column

1 Answers1