Mysql returns some records null in field of date as date have multiple format

Question

I am trying to analyze order_Date column and column have multiple date format i want to convert all those date in same format which wull make be easier to analyze the order_date.

I am trying to analyze the order_date however this column have multiple date format 2019/07/15 and 1/13/2014

Howeever, while converting different format date with one format yyyy/mm/dd with query. select date_format(order_date, '%y/%m/%d'),orderid from superstore;

it shows null values like this.

i have tried to use `CAST as well but it shows every single value as null.

select case when order_date like '%Y' then date_format(order_date, '%Y/%m/%d') else null end as newdate from superstore;

Answer 1

date_format funtion is used to format a date datatype you should use https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date any null values returned by str_to_date either failed or started as null. You will need to examine these and adjust the str_to_date parameters appropriately. There is a catch though is 20/2/20 y/m/d or d/m/y (for example) and how can you differentiate month and day where both are <=12?

For example

drop table if exists t;
create table t
(dt varchar(10));
insert into t values
('1/1/2020'),('2020/1/12'),('12/12/12'),(null),('13-14-15');

select dt,
        case when length(substring_index(dt,'/',-1)) = 4  then str_to_date(dt,'%d/%m/%Y')
             when length(substring_index(dt,'/',1)) = 4  then str_to_date(dt,'%Y/%m/%d')
             when length(substring_index(dt,'/',1)) = 2  then str_to_date(dt,'%y/%m/%d')
             else str_to_date(dt,'%y/%m/%d')
        end  dateformatted
from t;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=553219f33ad9e9a4404fc4c0cb6571c9

note in no case can I identify month and day and sometimes year..