5

I am trying to insert date to a mysql table. The SQL query I used to create the table is as follows:

CREATE TABLE IF NOT EXISTS DDD(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE, 
    datecolumn DATE NOT NULL , 
    open FLOAT, 
    high FLOAT, 
    low FLOAT, 
    close FLOAT, 
    volume INT)

I am inserting the data with the following SQL statement:

INSERT INTO DDD(datecolumn, open, high, low, close, volume)
VALUES(2011-05-26, 12.09, 13.31, 12.05, 13.09, 1293441)

After that when I select and view the data the entries in the datecolumn is

0000-00-00

I have tried the solution from

importing CSVs into MySQL table leaves date fields as 0000-00-00

...but it resulted in the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'datecolumn DATE' at line 1

after the following query,

ALTER TABLE db.DDD MODIFY datecolumn datecolumn DATE;

I am a novice in SQL and learning now. Could anyone shed some light, what it is that I am doing wrong?

10

Put the date into quotation marks:

INSERT INTO DDD(datecolumn, open, high, low, close, volume) 
VALUES('2011-05-26', 12.09, 13.31, 12.05, 13.09, 1293441)

MySQL will have silently done some mathematics with 2011-05-26 and got its knickers in a twist.

It does warn you though:

mysql> insert into d VALUES(2011-05-26);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'datecolumn' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Without using quotes, MySQL is essentially inserting the integer 1980 ( which is 2011 minus 05 minus 26) as a date, as it treats it as mathematics:

mysql> select 2011-05-26;
+------------+
| 2011-05-26 |
+------------+
|       1980 |
+------------+
1 row in set (0.00 sec)

mysql>
  • Thanks Phil :). It worked. However, what was the trick behind the quotes? By mentioning within quotes, ain't we supply as strings? – RussellB May 23 '17 at 15:52
  • 1
    Certain strings (that look like dates or date+time) are automatically converted to the internal format for DATE or DATETIME or TIMESTAMP. – Rick James May 23 '17 at 17:32

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.