sql - changing format date MySql

Question

i have date type column in MySql. by default MySql date format is YYYY/MM/DD. but actualy i prefer to use DD/MM/YYYY format.

Could i change the column date format?

Answer 1

No, you can't change the default MySQL format for DATE, DATETIME or TIMESTAMP columns.

But you can use MySQL functions in your SQL statements to output a DATE expression as a string in different format.

DATE_FORMAT( datecol , '%m/%d/%Y')  AS datecol

(That will work fine in SELECT list, but avoid using this in any predicates (i.e. the WHERE clause). There, you'll want to reference the bare column, and convert strings of your preferred format 'MM/DD/YYYY' using the STR_TO_DATE function, e.g.

datecol >= STR_TO_DATE('07/16/2012','%m/%d/%Y')

---

With that said, I think you will really be better served using the MySQL default DATE format in your interactions with the database, and handling formatting changes in your code.