Database date format

[PRodgers4284]

I need to change the format that my database stores a date, it stores it as 0000/00/00, how can i change the format of this? My code validates the date format as eg "12/08/1980" and i would like this to be the format it is stored in the table.

[angst]

what type of data base is it?

[PRodgers4284]

what type of data base is it?

its a mysql database

[StupidRalph]

Use mysql's DATE_FORMAT()


SELECT DATE_FORMAT(`date_column`, '%m/%d/%Y') AS `formatted_date` FROM `table_name`;

[angst]

interesting, I haven't seen that one before.

[StupidRalph]

Its a really useful function.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

There are a lot of date functions that could be used instead of using PHP to convert things. My second favorite being DATE_SUB/DATE_ADD.

[angst]

yes, i try to do as much of the work as I can in the SQL statement without going into php whenever possable.

also yes, i'm aware of those functions. good stuff

[PRodgers4284]

Use mysql's DATE_FORMAT()


SELECT DATE_FORMAT(`date_column`, '%m/%d/%Y') AS `formatted_date` FROM `table_name`;


Is that all i need to do to get the format i want, just run that in mysql?

my code would then be:

SELECT DATE_FORMAT(`date_column`, '%m/%d/%Y') AS `formatted_date` FROM `users`;

[StupidRalph]

Is that all i need to do to get the format i want, just run that in mysql?

my code would then be:

SELECT DATE_FORMAT(`date_column`, '%m/%d/%Y') AS `formatted_date` FROM `users`;

Yes, but please note that I used a column named date_column as the first parameter. You should use whatever you named your date column. Or, if applicable, a string.
SELECT DATE_FORMAT('2008-02-06', '%m/%d/%Y') AS `formatted_date` FROM `users`;