How to Customize MySQL Date Formatting for Display and Programming Environments?

Understanding MySQL Date Format

MySQL stores dates internally in a packed three-byte integer format, consisting of day, month, and year values. However, when displayed, these dates appear as '0000-00-00' to maintain precision.

Custom Formatting with DATE_FORMAT()

To display dates in a specific format, such as 'd-m-Y', you can use the DATE_FORMAT() function. This function converts the date value to a human-readable string in the chosen format.

DATE_FORMAT() Syntax

The syntax for DATE_FORMAT() is as follows:

DATE_FORMAT(date_column, '%d-%m-%Y')

Example Usage

To select and display a date column in the 'd-m-Y' format, you can use the following query:

SELECT col1, col2, DATE_FORMAT(datecolumn, '%d-%m-%Y') AS datecolumn, more1...
FROM sometable
....

Note for Programming Environments

It's important to note that while using DATE_FORMAT() may be necessary for display purposes, when using the query in a programming environment, you should retrieve the raw date value. This value can then be formatted using the appropriate formatting function provided by your programming language.

The above is the detailed content of How to Customize MySQL Date Formatting for Display and Programming Environments?. For more information, please follow other related articles on the PHP Chinese website!