How to change MySQL date format for database?

mysql date formatting filemaker
Matt McCormick picture Matt McCormick · Oct 29, 2010 · Viewed 70.4k times · Source

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

Gabriel Sosa picture Gabriel Sosa · Oct 29, 2010

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.

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00 I would like to extract date from datetime and write a query like: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Is the …

Read More
how to convert a string to date in mysql?

I have a string column which acts as a date and I want to select it as a date. Is it possible? My sample data format would be; month/day/year -> 12/31/2011

Read More