MySQL Date Format and Representation

When creating a DATE field in MySQL, users may encounter issues with dates being stored in a non-user-friendly format like 0000-00-00. This article addresses the possibility of changing this format to 'd-m-Y' or any other preferred display format.

MySQL's Internal Date Storage

MySQL internally stores dates as a three-byte integer packed according to a specific formula: DD MM×32 YYYY×16×32. This representation is highly efficient for storage and computational purposes.

Displaying Dates in a Specific Format

While dates are stored internally in their compact form, for display purposes, they are often converted to a more readable format. This is where the DATE_FORMAT() function comes into play.

The DATE_FORMAT() function allows users to specify the desired output format for a date column. To achieve the 'd-m-Y' format, one can use the following syntax:

SELECT DATE_FORMAT(datecolumn, '%d-%m-%Y') AS formatted_date

This will convert the datecolumn into a VARCHAR with the specified format. It's important to note that this is primarily for display purposes and does not change the actual stored date value.

Considerations for Programming Tools

When working with dates in a programming environment, it's crucial to consider that the raw date value, not the display format, is what is needed. Therefore, using the DATE_FORMAT() function in queries for programming purposes is generally not recommended.

Instead, programming environments typically provide their own formatting functions that can be used to convert the raw date value into a desired display format for presentation purposes.