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

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'.

Answer 2

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.

Answer 3

Edit 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 4

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

Answer 5

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.

Answer 6

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.