MySQL: Convert from Varchar to Date and applying Date_Sub

Question

I am trying to use 'date-sub' to subtract few month using MySQL.

Before I do that, I figure I have to covert varchar(7) value into date format first. So, I used following:

 select str_to_date('7-2016', '%m-%Y');  -- 2016-07-00 (result)

The result was ok, but I am not sure why I get '-00'.

Now, I am trying to apply date_sub:

 select date_sub(str_to_date('7-2016', '%m-%Y'), interval 11 month);

Why do I get null data?

Answer 1

select str_to_date('7-2016', '%m-%Y');  -- 2016-07-00 (result)

Depends on SQL mode. In general - no. fiddle

---

Use simple

STR_TO_DATE(CONCAT('1-', @source_literal), '%d-%m-%Y') - INTERVAL 11 MONTH

fiddle