MySQL Union All Causing date field to lose format

Question

I have 3 subqueries that work fine individually. I am attempting to join them with Union All. The query runs and gives the correct number of results but the format of the date field is messed up; for example '2016-11-01' is showing as '2,016, -11, -01'. This is the same for every row.

I've had a search and have found some threads concerning aliases but they do not seem to be the problem here as the query is running fine, with the exception of the date format.

Any help would be appreciated, thanks.

Here is the code:

    # offers_8a
    SELECT advisor, ats_id, offer_date
    FROM max_offers
    WHERE offer_date > (CURDATE()- INTERVAL 135 DAY)
    GROUP BY advisor, ats_id, offer_date

    UNION ALL

    # offers_8b
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN source_tables.dates as dt
            ON ic.intake = dt.current_intake

    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY)

    UNION ALL 


    # offers_8c
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN 

            #s_prev_intake
            (SELECT current_next_intakes.current_intake, assign,         forecast_intake, forecast_intake_sugar

                FROM source_tables.dates
                    INNER JOIN source_tables.current_next_intakes
                        ON source_tables.dates.current_intake =  source_tables.current_next_intakes.current_intake

                WHERE assign = 'Prev') AS prv

            ON ic.intake = prv.forecast_intake


    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY);

Answer 1

I have fixed this by enclosing the Union in brackets and using it as a derived table in a Select query, didn't need to alter any formats etc.

Is it always best to do this with Union queries? The examples I found online did not show this.

Thanks

Answer 2

Use UNION instead of UNION ALL.