How to change MySQL date format for database?

Question

We are using a MySQL database with FileMaker. It appears that when FileMaker is reading the MySQL tables, it will only accept dates in the format of m/d/y.

Is there any way I can get our MySQL database to change its default format to be m/d/y instead of YYYY-MM-DD?

I know I can use the DATE_FORMAT() function on individual SELECT queries but I want to see if I can just change the default formatting.

Answer 1

Reading a little more I found you can change the format for an specific field but there is not recommended.

you cannot change the storage format

you could set ALLOW_INVALID_DATES and save the dates with the format you wish but I really don't recommend that.

if your field isn't indexed there is not issue on call DATE_FORMAT() when you are doing the select, the only issue is if you need to make a select for that field in which case the index wont be used because you are calling the function.

Answer 2

The trick is not to change the format in MySQL (it isn't stored as a string anyway), but to specify the format you want when you query it.

This can be achieved using something like this:

SELECT date_format(mydatefield,'%m/%d/%Y') as mydatefield FROM mytable

The official MySQL manual for this is here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

By the way, off-topic but I feel I should mention it: I'd always recommend against using mm/dd/yyyy as a date format -- you'll confuse anyone from outside the US; virtually every other country in the world would normally use dd/mm/yyyy. But both those formats are ambiguous - what date do you mean when you say "12/05/2010"? probably a different one from me, but it's impossible to actually know which way round you intended.

If you're intending to use the a date for display purposes, I'd always show the month as a word or abbreviation, as this removes any ambiguity. For input purposes, use a calendar control to avoid any possible confusion.