Change date format in mySql database table

Question

Morning everyone. Just a quick question. In my existing MySql database I've few columns with dates in it. Currently these are yyyy-mm-dd format but now I need to change it all to dd-mm-yyyy format. I've tried select date_format(curdate(), '%d/%m/%Y'); but it doesnt update existing data in my table.Is there any easy way of doing it? Or even if its not easy could you give me some suggestion how to do it please and thank you.

Solution 1

Try using

SELECT DATE_FORMAT(dateColumn,'%d/%m/%Y') AS dateColumn FROM table

Solution 2

You cann't able to change the default date format in table. Default DATE format is 'YYYY-MM-DD'. But you can able to retrive the date column value as your way.

Ref: http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Comments

[user2815059]

It kinda works Vijaykumar. It shows me the single column and the format is changed. But when I go back to my table the content is not affected by my query

[vhadalgi]

yes that's obvious,if you want this data you can redirect to some other table in your database + if this is working pls accept the answer

[user2815059]

Great stuff. Thanks for your help

[user2815059]

Thanks for the link. Its very handy

[Krishna Chalise]

INSERT INTO stocks_remaining(DATE_FORMAT(Date,"%m/%d/%Y"), Item, Quantity) VALUES("02/05/2017", "mouse", 2) What would be the right syntax for this