Mysql STR_TO_TIME formatting

My query looks like this:

select STR_TO_DATE( CONCAT(MONTH(date), '-', DAY(date) ), '%M-%d' ) from `subscriptions` 

But the end result is always records with null columns. What is wrong with this formatting?

Expected behaviour:

2019-04-20

Should become:

04-20

Please let me know.

Answer

A date does per definition contain a year so STR_TO_DATE will return NULL since it cannot return a date. You can compare STR_TO_DATE("2020-4-20", '%Y-%m-%d' ) with STR_TO_DATE("4-20", '%m-%d' ). The first one is working returning 2020-04-20 while the second one will return NULL.

If your goal here is to 0-pad the day and month, have a look at LPAD

SELECT CONCAT( LPAD(MONTH(NOW()), 2, 0), "-", LPAD(DAY(NOW()), 2, 0) )
+-----------------------------------------------------------------+
| CONCAT( LPAD(MONTH(NOW()), 2, 0), "-", LPAD(DAY(NOW()), 2, 0) ) |
+-----------------------------------------------------------------+
| 04-29                                                           |
+-----------------------------------------------------------------+

EDIT I think what you’re looking for is in the answer from @nbk; DATE_FORMAT.

SELECT DATE_FORMAT(NOW(), "%m-%d" )
+------------------------------+
| DATE_FORMAT(NOW(), "%m-%d" ) |
+------------------------------+
| 04-29                        |
+------------------------------+