I am trying to analyze the order_date column and the column has multiple date formats and I want to convert all these dates to the same format which will make the analysis order_date much easier easy.
I am trying to parse order_date but this column has multiple date formats 2019/07/15 and 1/13/2014
However, when using a query to convert dates in different formats into one format yyyy/mm/dd.
select date_format(order_date, '%y/%m/%d'),orderid from superstore;
It shows null value like this.
I also tried using "CAST" but it shows every value as empty.
Select order_date like '%Y', then date_format(order_date, '%Y/%m/%d') else null end as newdate from superstore;
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
date_format function is used to format the date data type you should use https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to -date Any null value returned by str_to_date either fails or starts with a null value. You need to check these and adjust the str_to_date parameters appropriately. But one question is 20/2/20 y/m/d or d/m/y (for example), how to distinguish the case where both month and day are 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=553219f33ad9e9a4404fc4c0cb65 71c9一个>
Please note that in no case am I able to identify the month and day, and sometimes even the year..