CODEWITHSUNDEEP
mysqlsqldateselectdate-formatting
Amy Neville
Amy Neville

Reputation: 10581

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

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

Upvotes: 9

Views: 7837

Answers (1)

Mureinik
Mureinik

Reputation: 311143

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

Upvotes: 14

Related Questions