Convert Dates to Strings in MySQL

In MySQL, you can convert dates to strings using the DATE_FORMAT() function, which allows you to specify the desired date format.

In this tutorial, you will learn how to use DATE_FORMAT() function to convert a give date object into a string, in the specified format.

Using DATE_FORMAT() to Convert Dates to Strings

The DATE_FORMAT() function in MySQL takes a date or datetime column as input and converts it to a string based on the specified format.

</>
Copy
DATE_FORMAT(date, format)

The format argument specifies how the date should be formatted in the string. For example, '%Y-%m-%d' for “2023-10-25” or '%d/%m/%Y' for “25/10/2023”.

Examples: Converting Dates to Strings in MySQL

We’ll go through a few examples to demonstrate how to use DATE_FORMAT() to convert dates to strings in various formats.

1. Converting a Date to “YYYY-MM-DD” Format

To convert a date to the YYYY-MM-DD format:

</>
Copy
SELECT DATE_FORMAT('2023-10-25', '%Y-%m-%d') AS formatted_date;

This query returns 2023-10-25, formatting the date in the standard YYYY-MM-DD format.

Convert Dates to Strings in MySQL - Example: Converting a Date to YYYY-MM-DD Format

2. Converting a Date to “Month Day, Year” Format

To display a date in the format “Month Day, Year” (e.g., “October 25, 2023”):

</>
Copy
SELECT DATE_FORMAT('2023-10-25', '%M %d, %Y') AS formatted_date;

This query returns October 25, 2023, converting the date to a readable string format.

Convert Dates to Strings in MySQL - Example: Converting a Date to Month Day, Year Format

3. Converting a DateTime to “DD/MM/YYYY HH:MM” Format

To convert a date and time to DD/MM/YYYY HH:MM format:

</>
Copy
SELECT DATE_FORMAT('2023-10-25 14:30:00', '%d/%m/%Y %H:%i') AS formatted_datetime;

This query returns 25/10/2023 14:30, formatting both date and time in a standard European format.

Convert Dates to Strings in MySQL - Example: Converting a DateTime to DD/MM/YYYY HH:MM Format

4. Using DATE_FORMAT() to Extract and Display the Year Only

To extract and display only the year from a date:

</>
Copy
SELECT DATE_FORMAT('2023-10-25', '%Y') AS year_only;

This query returns 2023, extracting only the year from the date string.

Convert Dates to Strings in MySQL - Example: Using DATE_FORMAT() to Extract and Display the Year Only

Using DATE_FORMAT() allows you to customize the display format of date and datetime values, making it easier to adapt date data to the format needed for reporting or presentation.