How to Change Multiple Date Formats in Same Column

How to change two different date format into single date format in excel

With data in A2, in B2 enter:

=IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)))

and apply your format to B2.

Then copy B2 downward:

The formula converts any text values in column A into true Excel dates in column B.

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.

Multiple date format in on column

Each string representation format potentially can require own conversion date style, therefore:

with cte as (
select '2017-06-14' DateString
union all
select '04/09/15' -- expected dateformat: dd/mm/yy
)

select 
case when DateString like '%-%-%' then CONVERT(DATE, DateString, 20) -- ODBC canonical
     when DateString like '%/%/%' then CONVERT(DATE, DateString, 3) -- French 
     else null
end
from cte

Results to:

2017-06-14
2015-09-04

Pandas: Multiple date formats in one column

If it's in a data frame use this:

from dateutil.parser import parse
import pandas as pd

for i in range(len(df['Date'])):
    df['Date'][i] = parse(df['Date'][i])
df['Date'] = pd.to_datetime(df['Date']).dt.strftime("%d-%m-%Y")

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)

how to convert multiple date formats in one format in pandas

try this:

df['Date'] = pd.to_datetime(df.Date)

df['Date'] = df['Date'].dt.strftime('%Y-%m-%d %H:%M')

link: Series.dt.strftime

Format multiple date formats in one columns using lubridate

From the help on parse_date_time:

## ** how to use select_formats **
## By default %Y has precedence:
parse_date_time(c("27-09-13", "27-09-2013"), "dmy")
## [1] "13-09-27 UTC"   "2013-09-27 UTC"

## to give priority to %y format, define your own select_format function:

my_select <-   function(trained){
  n_fmts <- nchar(gsub("[^%]", "", names(trained))) + grepl("%y",     names(trained))*1.5
  names(trained[ which.max(n_fmts) ])
}

parse_date_time(c("27-09-13", "27-09-2013"), "dmy", select_formats = my_select)
## '[1] "2013-09-27 UTC" "2013-09-27 UTC"

---

Related Topics