How Can I Extract the Month and Year in YYMM Format from a VARCHAR Date in MySQL?

Converting VARCHAR to Date with Month and Year Extraction in MySQL

To convert a VARCHAR value representing a date into a date-only value in MySQL, you can utilize the STR_TO_DATE and DATE_FORMAT functions. However, if you specifically require the month and year only in a YYMM format, you need to modify your approach.

Correcting Errors in STR_TO_DATE Conversion

Your usage of STR_TO_DATE attempts to convert a value in the format 'mm/dd/yyyy'. However, your sample input value ('1/9/2011') doesn't match this format. As a result, you obtain unexpected outputs like '2009-01-00'.

SOLUTION: Using DATE_FORMAT and STR_TO_DATE

To obtain the month and year in YYMM format, you can combine the DATE_FORMAT and STR_TO_DATE functions as follows:

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

This expression will convert the input VARCHAR value to a date and then format it as 'yyyymm'. The final result will be '201101', as you desired.

Additional Note:

The order of day (dd), month (mm), and year (yyyy) in the STR_TO_DATE function should match the format of your input VARCHAR value. Adjust the format string accordingly if the order differs in your case.

The above is the detailed content of How Can I Extract the Month and Year in YYMM Format from a VARCHAR Date in MySQL?. For more information, please follow other related articles on the PHP Chinese website!