changing date format in mysql

[Anon]

i have a problem in data format currently i am storing date in format like yyyy-mm-dd in table but i want to store the date like dd-mm-yyyy

so u help me in solving this problem

thanks

[danwatt]

First off, are you storing as a string or as a DATE column? That might help someone better provide an answer.

DATE colum = YYYY-MM-DD, but allows you to use certain functions on this column, for example
select YEAR(somecolumn)...

[Anon]

Hi,
What Dan said was right.If you want to save the date in dd-mm-yyyy format you have no other go except change the data type of the column to String.
I don't think you worry about the format in which the date has been saved.You can use MySQL date functions to format the date in your required format while retrieving the data.You can use PHP also to format the date.

Cheers
Ajay

[danwatt]

Right...
SELECT date_format(colname,'%d-%m-%Y') FROM db

would accomplish just that. In fact, its probably easier / faster to let MySQL do this for you. Of course, this example is assuming that you are using a DATE column.

IF you are currently using a string column, you have 3 options:
1) Select every record, retrieve the date and let PHP reformat the date, and update the records (You might be able to do this all in MySQL, but I do not know what the syntax would be)
2) Add / alter a date column. I dont know if there is a way to alter a string to date, but it might be possible. If not, add a new column, manually copy the dates using PHP, then delete the old column.
3) Whenever you retrieve data, use PHP to manually transform the date before it gets displayed.