Remove leading zeros from %m and %d in MySQL date_format

Question

The following query produces a date that looks like this 2016, 01, 02. How do I get it to remove trailing zeros from the month and day so that it looks like this 2016, 1, 2?

SELECT DATE_FORMAT(earning_created, '%Y, %m, %d') AS day, SUM(earning_amount) AS amount
FROM earnings
WHERE earning_account_id = ?
GROUP BY DATE(earning_created)
ORDER BY earning_created

Answer 1

You can use %c to format the month without the leading zero and %e to format the day of the month:

SELECT   DATE_FORMAT(earning_created, '%Y, %c, %e') AS day, -- Here! 
         SUM(earning_amount) AS amount
FROM     earnings
WHERE    earning_account_id = ?
GROUP BY DATE(earning_created)
ORDER BY earning_created