While inserting to table date going as 000000 in mysql I have a table with column datatype as Date in mysql database.
I want to insert dates in 'dd/mm/yyyy' format so I used below query. When I use only the SELECT, the result set is showing correct. But when I use the INSERT, it is going as '000000' (the values inserted are all the '0000-00-00' date in abc).

The column I am selecting from (completion_date) is varchar datatype.

INSERT INTO abc 
    (maturity_date) -- Datatype Date for maturity_date column 
SELECT 
    DATE_FORMAT(
        STR_TO_DATE(completion_date,    -- varchar
                    '%d/%m/%Y'), 
        '%d-%m-%Y') 
FROM XYZ ;


When if am using below script, data is inserted properly but format is 'yyyy/mm/dd'. 

INSERT ...
SELECT STR_TO_DATE(completion_date , '%d-%m-%Y')
...


Is there any way to insert in 'dd/mm/yyyy' format with proper data?

example: '21-01-2017'
From the MySQL manual, DATE uses the YYYY-MM-DD format
https://dev.mysql.com/doc/refman/5.7/en/datetime.html

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

You can select dates out of your database and convert them to whatever format you want to display to your uses, but to store them you'll need to use the YYYY-MM-DD format that MySQL is expecting.