How Can I Customize the DATETIME Format When Creating a MySQL Table?

Customizing DATETIME Format During Table Creation in MySQL

When creating a new table in MySQL, users often encounter the need to specify a default format for DATETIME columns. While the default format for DATETIME is 'YYYY-MM-DD HH:MM:SS', it is possible to customize this format to suit specific requirements.

As mentioned in the original question, a common desired format is 'DD-MM-YYYY HH:MM:SS'. While this cannot be directly achieved using the standard CREATE TABLE syntax, there are alternative approaches to achieve this customization.

Using the DATE_FORMAT Function

Instead of specifying the desired format directly during table creation, the DATE_FORMAT function can be utilized to convert the stored DATETIME value to the desired format when it is retrieved. The DATE_FORMAT function takes the following syntax:

DATE_FORMAT(date_expression, format_string)

where date expression is the DATETIME column or expression and format_string specifies the desired output format.

Example:

To create a DATETIME column and retrieve its values in the 'DD-MM-YYYY HH:MM:SS' format, the following query can be used:

CREATE TABLE my_table (datetime_column DATETIME);
SELECT DATE_FORMAT(datetime_column, '%d-%m-%Y %H:%i:%s') FROM my_table;

This query creates a new table with a DATETIME column and retrieves its values in the specified format.

Note: It is important to note that the DATE_FORMAT function does not alter the stored DATETIME value but only changes the format in which it is displayed.

The above is the detailed content of How Can I Customize the DATETIME Format When Creating a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!