Reformatting date and timestamp with r

Question

In order to prevent an error in uploading xls data into a sql database, I am trying to reformat a date type of "08/22/2019 02:05 PM CDT" and want only the date, not the time or the timezone. Many efforts to use the default, POSIX and lubridate actions have failed. The xls file formats the date column as general.

I have a column of data to convert, not a single cell. This is a part of a loop for multiple files in a folder.

Failures:

 #mydata_r11_Date2 <- strptime(as.character(mydata_r11_Date$Date), "%d/%m/%Y")
  # parse_date_time(x = mydata_r11_Date$Date,
  #                 orders = c("d m y", "d B Y", "m/d/y"),
  #                 locale = "eng")
  # 
  # 
  # mydata_r11_Date <- as.character(mydata_r11_Date)
  mydata_r11_Date <- gsub('^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\\.[0-9]+[+-][0-9]{2}):([0-9]{2})$',
             '\\1\\2',
             mydata_r11_Date$Date)

  ymd_hms(mydata_r11_Date$Date)

  mydata_r11_Date <- as_date (mydata_r11_Date$Date,format = "%Y-%m-%d")

  mydata_r11_Date2 <- format(as.Date(mydata_r11_Date,"%Y-%m-%d"),"%Y-%m-%d")

Errors include:

1. Warning message: All formats failed to parse. No formats found.

2. Error in as.Date.default(x, ...) : do not know how to convert 'x' to class “Date”

3. Error in as.Date.default(mydata_r11_Date$Date, format = "%Y-%m-%d") : do not know how to convert 'mydata_r11_Date$Date' to class “Date”

4. Error: unexpected ',' in " mydata_r11_Date <- as.Date(mydata_r11_Date$Date),"

5. Error in as_date(x) : object 'x' not found

   library(readxl)library(reshape2) library(lubridate)

   import xsl

   mydata_r11 <- read_excel("C:/FOLDER/FOLDER/FOLDER/OUTPUT/WADUJONOKO_student_assessment_results.xls",1,skip = 1, col_types = "list")

   Isolate date column

   mydata_r11_Date <- mydata_r11[,c(8)]

   Convert date

   mydata_r11_Date 2 <-

Have "08/22/2019 02:05 PM CDT" Want "08/22/2019"

Answer 1

I don't understand why you are resorting to complex regex here when you seem to only want the date component, which is the first 10 characters of the timestamps. Just take the substring and then call as.Date with an appropriate format mask:

x <- "08/22/2019 02:05 PM CDT"
y <- substr(x, 1, 10)
as.Date(y, format = "%m/%d/%Y")

[1] "2019-08-22"