I have created table called event and it has column birthday which is using the date format of
%Y-%m-%d but I want it to be %d-%m-%Y. so, how can i update it?
when i enter new row with the format %d-%m-%Y it converts it to 0000-00-00 (%Y-%m-%d)
I tried following Queries to change format but it isn't working.
SELECT DATE_FORMAT(CURDATE(), '%d/%m/%Y')
SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename
After executing this query it says rows affected and making all the values 0000-00-00.
Try this :
SELECT DATE_FORMAT(dateColumn,'%d/%m/%Y') AS dateColumn FROM table
This should work. I tried and works for me.
select DATE_FORMAT(birthday , '%d/%m/%Y') from event;
But what data type you have for birthday?
See this example.
If you want to insert (or update) a date which isn't in the right format u can use
INSERT INTO `event` (columname) VALUES (STR_TO_DATE('30/09/2014', '%d/%m/%Y'))