0

I'm using R. The data is coming from a SQL database, and it's being imported as a string/char. I'm trying to convert this string ("2021-05-19T19:30:00.000Z") into a datetime. There are multiples in the dataset like this, and some look normal (year-month-day i.e. 2021-05-17) which are easy to convert. It's just this format of the string I'm having difficulties with. I have tried the following which are the answers to similar questions on this site, but both return NAs.

test_string <- "2021-05-19T19:30:00.000Z"
converted_string <- as.POSIXct(test_string, format="%Y-%m-%dT%H:%M:%SZ")

And

converted_string <- lubridate::ymd_hm(test_string)

Furthermore, would it be possible to solve this using lubridate? I'm working on the cloud, and it is difficult for me to install new packages.

Michael Schulte
  • 45
  • 4
  • Drop the Z at the end and the time will convert to the second: `as.POSIXct("2021-05-19T19:30:00.000Z",format="%Y-%m-%dT%H:%M:%S")` – Dave2e Sep 09 '22 at 21:05
  • Please post the data reading code. All database products (except SQLite) have date types. Why is the data read as text in the first place? Is it stored in a `varchar` or `nvarchar` field? That's a serious database design bug that needs to be fixed. If the field is a `datetime` or `datetimeoffset` though, the R code shouldn't be converting it to text only to parse it back to a date – Panagiotis Kanavos Dec 13 '22 at 15:16
  • 1
    `2021-05-19T19:30:00.000Z` is a standard ISO8601 date with UTC time. `Z` refers to UTC and is equivalent to `+00:00`. On the other hand `2021-05-19` is an ISO8601 date without a time part. If the database uses a text field with mixed contents, you're in trouble. Nothing says you won't find `12/31/2022` at some point. Or worse, `4/7/2022` - is that M/D or D/M ? – Panagiotis Kanavos Dec 13 '22 at 15:19

0 Answers0