Changing date format in MySQL table
 

Hello All,

I have a Bugzilla installation that uses MySQL DB (of course). The date tables in MySQL currently have the following format:

mm-dd-yyyy

We would like to change it to:

dd/mm/yyyy

Would anybody know a quick hack for this?

You can always change the format in your query like this:

SELECT DATE_FORMAT(thedatetime,'%d/%m/%y %H:%i:%s') FROM ...

I don't know if it's possible to change the format mysql uses by default.

Does MySQL have a set format or do you set it when you create the table?

By the way, does MySQL have a history file or log file somewhere where I can check the sql statements to see how that table was created?

I'm guessing that you can't change the internal MySQL date format because if you do, all the date functions will return wrong results. But I'm not sure about that.

I always create my tables by using a script file so that I still know what I've done later on. If you don't have such a script, you can always do some reverse engineering to generate one. There are several tools to do this, check out http://www.datanamic.com/ to see if they have any tools to help you out.

On the logging question, I have read in MySQL docs that indeed it does have a query log file, BUT it is not default and you have to flag it on start in order for it to work :-( ...but again any ideas are welcome :-)

On the reverse engineering, even though it sounds cool, we are a bit strapped for time, but if we get an extension I'll definitley check it out....this is not the only issue we friction with.

I'll keep on looking to see what I can find on changing the date format in the table itself....

Good luck, and let me know if you find anything!

Thanks and I will!

I am using the excellent EMS MySQL Manager here in the field management you can easily change the date format no problems.

http://www.ems-hitech.com/index.phtml

Brilliant software.

Also according to previous posting above:-

SELECT DATE_FORMAT(dated,'%d/%m/%y') FROM info;

This works fine on my system.

Although this software changes the date format at presentation level, the format in the database stays the same.