Question 20780 in Mysql Question posted in
The official MySQL documentation can be found here.

How to insert date in a new table in YYYY-MM-DD' format? – Mysql

CREATE TABLE ORDERS (
    ORD_NUM NUMERIC(6,0) NOT NULL PRIMARY KEY, 
    ORD_AMOUNT NUMERIC(12,2) NOT NULL, 
    ORD_DATE DATE NOT NULL, 

INSERT INTO ORDERS VALUES('200100', '1000.00', '08/01/2008');
INSERT INTO ORDERS VALUES('200110', '3000.00', '04/15/2008');
INSERT INTO ORDERS VALUES('200107', '4500.00', '08/30/2008');

Since I have large number of rows with date in the above format, how can I convert the into yyyy-mm-dd format?

The below solution does not work as the values are not inserted into the ORDERS table.
So, nothing to update.

UPDATE ORDERS
SET ORD_DATE = DATE_FORMAT(ORD_DATE, '%Y-%m-%d');

Executing the code gives error, which I learned to be due to the date format, which MySQL does not allow.

Tags:

2

Answers


  1. 0

    You just need to convert the string to a date in your insert statements:

    INSERT INTO ORDERS VALUES('200100', '1000.00', TO_DATE('08/01/2008', 'mm/dd/yyyy');
    Login or Signup to reply.
  2. 0

    The question misunderstands how dates work in SQL. The Date type does not have ANY human-readable format at all. The values in this column will be stored as binary data, which has significant benefits over string formats for memory/storage use, date math, and indexing.

    Now we insert a value like '08/01/2008' into a date column. I will interpret this to mean August 1st based on the other values in the question (this isn’t a universal or even majority interpretation!). This value provides the month first, then the day, then the year… but MySQL will not store it that way and does not preserve the original format.

    Therefore it makes no sense at all to UPDATE the column to set a specific format. You can’t do it, because dates are not stored in a way that preserves any write-able format.

    What you can do is format the value at output time, as part of a SELECT query, to use whatever format you need. Additionally, you can use the Str_To_Date() function to control how string values will be interpreted when creating or comparing to native SQL dates.

    One thing to keep in mind: thanks to cultural/internationalization issues, converting dates (and numbers!) to and from strings is much slower and more error-prone for a computer than you likely expect. It’s something to avoid. Therefore, converting to the native date format early, and leaving it that way as long as possible, is usually the best option.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search