How to Convert VARCHAR to Date in MySQL and Extract Only Month and Year

Many developers encounter the need to convert a VARCHAR value representing a date into a MySQL date format and extract specific date components like month and year. This article provides a solution to this common challenge by walking you through the steps involved.

The STR_TO_DATE function is a MySQL function commonly used to convert strings into date values. However, using it directly may lead to unexpected results, especially if the string's format is different from the format specified in the function.

To address this issue, a combination of STR_TO_DATE and DATE_FORMAT functions can be employed. DATE_FORMAT allows you to format a date value into a specific string representation.

To convert a VARCHAR value like '1/9/2011' to a date and extract only the month and year (in the format 'YYYYMM'), use the following query:

SELECT DATE_FORMAT(STR_TO_DATE('1/9/2011', '%m/%d/%Y'), '%Y%m');

Alternatively, if the VARCHAR value is in 'MM/dd/YYYY' format, use this query:

SELECT DATE_FORMAT(STR_TO_DATE('12/31/2011', '%m/%d/%Y'), '%Y%m');

This approach ensures accurate conversion of the VARCHAR value to a date, and then extracts only the desired date components using DATE_FORMAT.