How Can I Display DATETIME Values in a Custom Format (e.g., DD-MM-YYYY HH:MM:SS) in MySQL?

Customizing DATETIME Format in MySQL Table Creation

Despite extensive research, you're seeking a solution to establish a new table with a DATETIME column featuring a predefined format of 'DD-MM-YYYY HH:MM:SS.'

MySQL inherently stores DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. While you cannot alter the storage format, you have options to customize the display format when required.

Leveraging Time Format Functions

MySQL provides a plethora of time format functions to transform the display format of DATETIME values. One such function is DATE_FORMAT, which allows you to specify the desired format:

SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM tablename;

This query will retrieve the DATETIME value from the specified column and display it in the 'MM/DD/YYYY HH:MM' format. You can substitute the format string with the desired pattern as per your requirements.

Example:

CREATE TABLE orders (
  order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Even though the DATETIME column in the 'orders' table stores values in the default MySQL format, you can use the DATE_FORMAT function to display them in the preferred 'DD-MM-YYYY HH:MM:SS' format:

SELECT order_date AS formatted_order_date,
       DATE_FORMAT(order_date, '%d-%m-%Y %h:%i:%s') AS custom_order_date
FROM orders;

This query will produce two columns: 'formatted_order_date' with the original datetime value in MySQL format and 'custom_order_date' with the value in 'DD-MM-YYYY HH:MM:SS' format.

The above is the detailed content of How Can I Display DATETIME Values in a Custom Format (e.g., DD-MM-YYYY HH:MM:SS) in MySQL?. For more information, please follow other related articles on the PHP Chinese website!