how to change default date format when creating table in MYSQL

Question

how to change default date format when creating table in MYSQL

Answer 1

You can't change the default format for a date during the table definition stage. (It must always obey the DATETIME, DATE or TIMESTAMP formats.) As the manual puts it:

Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

See the date and time reference docs for more info.

As such, you'll have to use the DATE_FORMAT() function at the point of output to achieve this goal.

Answer 2

You may want to use the STR_TO_DATE() and DATE_FORMAT() functions to communicate with MySQL using different date formats.

Example using STR_TO_DATE():

SELECT STR_TO_DATE('15-Dec-09 1:00:00 PM', '%d-%b-%y %h:%i:%S %p') AS date;
+---------------------+
| date                |
+---------------------+
| 2009-12-15 13:00:00 |
+---------------------+
1 row in set (0.07 sec)

Example using DATE_FORMAT():

SELECT DATE_FORMAT('2009-12-15 13:00:00', '%d-%b-%y %h:%i:%S %p') AS date;
+-----------------------+
| date                  |
+-----------------------+
| 15-Dec-09 01:00:00 PM |
+-----------------------+
1 row in set (0.00 sec)