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.

    (maturity_date) -- Datatype Date for maturity_date column 
        STR_TO_DATE(completion_date,    -- varchar

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

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'

  • Show us the whole INSERT statement (by editing your question). Dec 12, 2017 at 10:21
  • 1
    Give us the output of SHOW CREATE TABLE abc\G (it's really called that?). Put this IN THE QUESTION! You can always edit your own question!
    – Vérace
    Dec 12, 2017 at 10:53
  • 2
    Dates should be stored in DATEs. Non-standard output formats should be applied by SELECT DATE_FORMAT(...). The only format for the DATE datatype is YYYY-MM-DD.
    – Rick James
    Dec 12, 2017 at 16:22
  • 1
    I'm having issues understanding the following sentence in your question: want to insert date in 'dd/mm/yyyy' format so using below query but selecting result set is showing correct but while inserting it is going as '000000' How can the it be possible that "...it is correct but showing '000000'..."? Please edit your question and try to rephrase it. We understand that English might not be your native language and that it might be difficult to express you issue. We do try to accommodate for that, but sometimes it is difficult for us.
    – John K. N.
    Dec 13, 2017 at 9:55
  • 1
    @hot2use Thank you for you information. I have corrected the question but it showing as hold. while inserting value going as ''0000-00-00', after reading some stuff and suggestion I got to know that only we can insert data in mysql as 'yyyy/mm/d' format.
    – Neminath
    Dec 13, 2017 at 10:16

1 Answer 1


From the MySQL manual, DATE uses the YYYY-MM-DD format


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.