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.

Yajairayajurveda

What sort of access does FileMaker need -- hopefully just read-only? Reason I ask is you might be able to use a view to provide the data to FileMaker which would allow you to specify the date format... – Immune

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.

Funchal

You can get around the index issue by aliasing the format output to something other than the field name, and using the original field name in the where clause. Though, mysql should be smart enough to realize this on its own. – Canaliculus

yes. I guess what I'm referring is to make a query with a WHERE like SELECT ... FROM table WHERE DATE_FORMAT(myfield, '%d/%m/%y') = '16/12/2010' in that case the index will not be used – Funchal

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.

Dustindustman

Answer 3

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

Upsurge

Answer 4

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.

Samples

Answer 5

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.

Domain

Answer 6

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

Kruse

Add some comments in your question and describe it. Even if it's so basic; this way it will be useful for beginners too. – Moseley

"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." said OP in 2010. – Titulary