krishna Prasad krishna Prasad - 3 months ago 9
MySQL Question

How to get valid dates from a varchar colum where dates are stored in different formats

I have a column in a table in MySql, which contains dates as string in any format (there is not fixed pattern) it could be as

m-d-y
or
M-D-Y
or
m/d/y
or YY/MM/DD or MM/DD/YY or mm/dd/yy etc. My question is how should I detect it and then change to some particular format i.e mm/dd/yy.

Answer

If you can guess the number of different formats you stored the dates in a varchar field then it would be easier to deal with the problem;

One way would be put all those different formats in the query given below:

SELECT 
COALESCE(
  STR_TO_DATE(your_date_column,'%m-%d-%Y'),
  STR_TO_DATE(your_date_column,'%M-%D-%Y'),
  STR_TO_DATE(your_date_column,'%m/%d/%Y'),
  STR_TO_DATE(your_date_column,'%m-%d-%Y'),
  ....
  .
  .

)
FROM your_table;

Demonstration:

SET @your_date_field := '8/8/2016';

SELECT 
COALESCE(
  STR_TO_DATE(@your_date_field,'%m-%d-%Y'),
  STR_TO_DATE(@your_date_field,'%M-%D-%Y'),
  STR_TO_DATE(@your_date_field,'%m/%d/%Y'),
  STR_TO_DATE(@your_date_field,'%M/%D/%Y')
);

Output: 2016-08-08 (yyyy-mm-dd)

Note:

But dates should be stored in a date datatype. Violating this will put towards this kind of cumbersome situation.

So, better move these date strings to date datatype column.


More: In order to move these date strings to a date datatype column you can follow the steps below:

ALTER TABLE your_table ADD COLUMN date_new date; 

UPDATE 
your_table
SET date_new = COALESCE(
  STR_TO_DATE(your_date_column,'%m-%d-%Y'),
  STR_TO_DATE(your_date_column,'%M-%D-%Y'),
  STR_TO_DATE(your_date_column,'%m/%d/%Y'),
  STR_TO_DATE(your_date_column,'%m-%d-%Y'),
  ....
  .
  .

);


ALTER TABLE your_table DROP COLUMN `date`;

ALTER TABLE datestable CHANGE COLUMN `date_new` `date` date;
Comments