How to change MySQL date format for database?

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 for开发者_StackOverflow社区mat 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.

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.

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.

I don't think you can, see Changing MySQL's Date Format.

There are system variables called date_format and datetime_format that look promising, but if you try to set them, you'll get an error, and the documentation say they are 'unused'.

I use a WAMP installation and usually simply create a column INT(10) and then store my dates like this:

UPDATE  `test` SET  `dateandtime` = DATE_FORMAT( NOW(),  '%y%m%d%H%i' ) WHERE `id` =1234;

This stores 2013-11-22 12:45:09 as a number like 1322111245. Yes, it may be considered "improper" but I don't care. It works, and I can sort easily and format on the client any which way I like.

This is obviously not suggested if you expect to run any other date functions, but for me, I usually just want to know the record's last update and sort a result set by date.

SELECT COUNT(field_name) AS count_it FROM table_name WHERE DATE_FORMAT(date_field,'%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d')-- to count records for today.

SELECT DATE_FORMAT(NAME_COLUMN, "%d/%l/%Y %H:%i:%s") AS 'NAME'