How to remove leading zero from date I have a query in which i have used the following

date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),INTERVAL (tt.day_id- 1 )DAY) as date


Datatype of the field is datetime and the result date is like this

2015-04-06


but the desired result is like this

2015-04-6


I don't need 0 in front of day. I tried this

date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%e'),INTERVAL (tt.day_id- 1 )DAY) as date


but didn't work. Can anyone help please!
You have the functions applied in wrong order. You need to do date arithmetic first (date_add, whatever) and apply the format last:

DATE_FORMAT( DATE_ADD(ss.week_startdate, INTERVAL (tt.day_id - 1) DAY),
             '%Y-%m-%e')


You also need %Y for a 4-digit year, not %y.



Also note that it's better to also change the first query, as it does a lot of redundant operations:

date_add(
    DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),
                INTERVAL (tt.day_id- 1 )DAY
               )
as date



First, it calculates: DATE_FORMAT(ss.week_startdate,'%y-%m-%d')
which converts the datetime value to a string with format (example for current date, 2015 April 28): '15-04-28'.
Then, it does: date_add( previous_result,  INTERVAL (tt.day_id-1) DAY)
But the previous_result is a string so it has to be converted to date or datetime. It's only by accident that the 2-year format does not lead to a wrong date. After this conversion, the date_add() is calculated and because the first argument (after the conversion) is a date and you only add days, not hours or smaller unit, the result is also a date.
Then, the as day is considered but this does nothing, as the previous operation has resulted in a date, too.
Finally, the result is shown in the default format for dates, which (by one more accident!) is %Y-%m-%d, to get the wanted format.


In all, three conversions (datetime -&gt; char -&gt; date -&gt; char), while you only need one.