2

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'

10
  • Show us the whole INSERT statement (by editing your question). – ypercubeᵀᴹ Dec 12 '17 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 '17 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 '17 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 '17 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 '17 at 10:16
3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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