CODEWITHSUNDEEP
sql
J.Zil
J.Zil

Reputation: 2449

SQL data truncation for date value

I'm having a hard time creating a simple table:

CREATE TABLE `csat` (
    `csat_id` INT NOT NULL AUTO_INCREMENT,
    `value` INT,
    `month` DATE NOT NULL,
    PRIMARY KEY (`csat_id`)
);

CREATE TABLE `migrated` (
    `migrated_id` INT NOT NULL AUTO_INCREMENT,
    `title` INT,
    `description` INT,
    `month` DATE NOT NULL,
    PRIMARY KEY (`migrated_id`)
);

INSERT INTO csat
VALUES (1, 1, 2017-06-15);

INSERT INTO migrated
VALUES (1, 2, 2018-06-15);

I get the error: Data truncation: Incorrect date value: '1996' for column 'month' at row 1

It seems like my date is in the right format: https://www.w3schools.com/sql/func_mysql_date.asp

I'm also wondering why I need to specify a value on the csat_id, because I thought SQL would just put that in for me since its the primary key.

Upvotes: 0

Views: 246

Answers (3)

Himanshu
Himanshu

Reputation: 3970

I guess you missed the single qoutes (as per Sql standards) at first in your date and then while inserting even if the column is autoincrement you need to specify columns other than the autoincrement column so as to make sure the data you are inserting belongs to that specific column or not Try this 

INSERT INTO 
  csat(value,month) values 
  (1,'2017-06-15')

Upvotes: 1

Simon R
Simon R

Reputation: 3772

You haven't said which database server you're using, but generally speaking dates are inputted as strings.

You should try the following inserts;

INSERT INTO csat (`csat_id`, `value`, `month`)
VALUES (1, 1, '2017-06-15');

INSERT INTO migrated (`migrated_id`, `title`, `description`, `month`)
VALUES (1, 2, 2, '2018-06-15');

Also, you should specify which columns you're inserting into. This prevents data from being entered into the wrong fields, especially when schema changes occur.

SQL does auto increment primary key fields (if defined that way). However, you had to define it in your insert statements because you didn't specify the columns you were inserting to.

Try this instead;

INSERT INTO csat (`value`, `month`)
VALUES (1, '2017-06-15');

INSERT INTO migrated (`title`, `description`, `month`)
VALUES (2, 2, '2018-06-15');

Upvotes: 1

Zack
Zack

Reputation: 2341

You have to wrap your date values in single quotation marks: '2017-06-15', not 2017-06-15. Right now, MySQL is evaluating this as 2017 minus 6 minus 15, which comes to 1996.

Also, when inserting, it's best to specify the columns you're inserting into. And if your column is set to AUTO_INCREMENT, you don't need to specify it:

INSERT INTO csat
(`value`, `month`)
VALUES 
(1, '2017-06-15');

I would also consider changing your column names. Perhaps make "value" more descriptive (value of what?) And month is misleading, since it's actually a date-type column.

Upvotes: 3

Related Questions