Converting to Date in a Character Column That Contains Two Date Formats

How to convert character to date with two different types of date formats in R?

You can do something like:

format_ymd  <- as.Date(dates, format = "%Y-%m-%d")
format_dmy  <- as.Date(dates, format = "%d/%m/%Y")
as.Date(ifelse(is.na(format_ymd), format_dmy, format_ymd), origin = "1970-01-01")
# [1] "2022-04-08" "2021-01-26" "2021-07-14" "2021-12-27"

cast column to_date with multiple date formats in original text column in postgresql

Both formats would be converted to dates with:

SELECT event_date::date event_date
FROM tablename;

Or use a CASE expression to choose one of the two formats:

SELECT TO_DATE(
         event_date,
         CASE
           WHEN event_date LIKE '____-__-__' THEN 'YYYY-MM-DD'
           WHEN event_date LIKE '________' THEN 'YYYYMMDD'
         END
       ) event_date
FROM tablename;

See the demo.

Converting multiple date formats in a column to a single form

There's no easy or simple solution for this, you'll have to at least go through the formats by hand. But saying that, the pseudocode for solution would be something like this:

tmp <- grepl('[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]', dob))
formats[tmp] <- '%d/%m/%Y'
tmp <- grepl( other format, dob)
formats[tmp] <- 'other format'
etc...
dates <- strptime(dob, formats)

Oracle Varchar2 column with multiple date formats

Said that this is really a bad and dangerous way to store dates, you could try with something like the following:

select myColumn,
       coalesce(
                to_date(myColumn default null on conversion error, 'dd/mm/yyyy' ),
                to_date(myColumn default null on conversion error, 'yyyy mm dd' ),
                ...                                                 /* all the known formats */
                to_date(myColumn default null on conversion error ) /* dangerous */
                )
from myTable

Here I would list all the possiblly expected formats, in the right order and trying to avoid the to_date without format mask, which can be really dangerous and give you unexpected results.

R Convert to date from multiple formats

This is pretty much I wrote the anytime package for:

R> dates <- c("01-01-2017","02-01-2017","12-01-2016","20160901","20161001", 
+             "20161101")
R> library(anytime)
R> anydate(dates)
[1] "2017-01-01" "2017-02-01" "2016-12-01" "2016-09-01" 
[5] "2016-10-01" "2016-11-01"
R> 

Parse any sane input reliably and without explicit format or origin or other line noise.

That being said, not starting ISO style with the year is asking for potential trouble, so 02-03-2017 could be February 3 or March 2. I am following the North American convention I too consider somewhat broken -- but is so darn prevalent. Do yourself a favour and try to limit inputs to ISO dates, at least ISO order YYYYMMDD.

---

Related Topics