Date in m/d/y in timestamp. How to convert to dd-mm-yyyy in mysql-CodePudding

I have a DATETIME column like below one:

| XDATE           |
| 9/21/03 00:00:00|
| 8/12/10 00:00:00|
| 7/30/01 00:00:00| 
| 7/1/03 00:00:00 |
| 6/16/20 00:00:00|
| 5/31/04 00:00:00|
| 11/7/05 00:00:00|
| 1/8/06 00:00:00 |

I want to convert XDATE column to “DD-MMYYYY” format.

I tried below

date_format(Xdate, '%d-%m-%Y').

But the result has null because it considers the format yyyy-mm-dd and converts to dd-mm-yyyy.

| XDATE           |
| 9/21/03 00:00:00| null
| 8/12/10 00:00:00|
| 7/30/01 00:00:00| null
| 7/1/03 00:00:00 | 
| 6/16/20 00:00:00| null
| 5/31/04 00:00:00|
| 11/7/05 00:00:00|
| 1/8/06 00:00:00 |

How to solve this problem and convert Xdate column to show only the date, and format it by “DD-MMYYYY"

CodePudding user response：

Use :

create table test_tbl(
xdate varchar(50) );


INSERT INTO test_tbl
VALUES ('9/21/03 00:00:00'),
       ('8/12/10 00:00:00'),
        ('7/1/03 00:00:00') ;

SELECT   date_format(str_to_date(xdate, '%m/%d/%Y'), '%d-%m-%Y') as xdate
from test_tbl;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/126

For more info on date-format: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

For more info on str-to-date: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date