Question

How to convert date YYYYMMDD to YY-MM-DD in MySQL query?

Answer 1

To convert a date from YYYYMMDD to YY-MM-DD in MySQL, use the STR_TO_DATE function to parse the string as a date object and then format it with the DATE_FORMAT function to the desired format. Wrap the two functions together in your SELECT statement.

Step-by-step explanation:

To convert a date in the format YYYYMMDD to YY-MM-DD in a MySQL query, you can use the DATE_FORMAT and STR_TO_DATE functions. Here is a step-by-step explanation of how you can achieve this:

• First, use the STR_TO_DATE function to convert the YYYYMMDD string into a date object. For example, if your column name is date_col, the function will look like this: STR_TO_DATE(date_col, '%Y%m%d').
• Next, use the DATE_FORMAT function to format the date object into the YY-MM-DD format. You will nest the STR_TO_DATE function inside the DATE_FORMAT function like this: DATE_FORMAT(STR_TO_DATE(date_col, '%Y%m%d'), '%y-%m-%d').

Here is a complete MySQL query example:

SELECT DATE_FORMAT(STR_TO_DATE(date_col, '%Y%m%d'), '%y-%m-%d') AS formatted_date FROM your_table;

This query will select the date from the date_col column, convert it to a date object, and then format it to YY-MM-DD. The result will be output under the alias formatted_date.