CODEWITHSUNDEEP
mysqlsqldatedatetimedate-format
Soviet Coder
Soviet Coder

Reputation: 1

Format date SQL from dd/mm/yy hh:mm to yy-mm-dd

I have a table that's using this format for dates

dd/mm/yy hh:mm

visit_date visit datetime
#VALUE! 01/01/2016 00:00
#VALUE! 02/01/2016 01:00

I need to change that format to

yy-mm-dd

So i can fill the visit_date Value with the proper value

Expected output

visit_date visit datetime
2016-01-01 01/01/2016 00:00
2016-01-02 02/01/2016 01:00

Thanks in advance to anyone who helps

Upvotes: 0

Views: 71

Answers (1)

Edward Radcliffe
Edward Radcliffe

Reputation: 587

SELECT    
     DATE_FORMAT(STR_TO_DATE(visit, "%m/%d/%Y"), "%Y-%m-%d") 
FROM t1

Fiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=93c4286c752026145a685af98c7773aa

Explanation: STR_TO_DATE() parses the 'laxed' DATETIME of your column. DATE_FORMAT() takes that and formats in the DATE format you're asking for.

Upvotes: 1

Related Questions