CODEWITHSUNDEEP
mysqldatedate-format
JustineP
JustineP

Reputation: 63

MySQL inserting date

This is my MySQL Code:

UPDATE student SET birthDate = STR_TO_DATE(birthDate, '%M %d,%Y');

INSERT INTO student (`birthDate`) VALUES ('June 10,1997');

But it displays an error:

Error code 1292, SQL state 22001: Data truncation: Incorrect date value: 'June 10,1997' for column 'birthDate' at row 1

Upvotes: 2

Views: 21963

Answers (4)

Kenneth
Kenneth

Reputation: 555

STR_TO_DATE() returns a properly formatted date based on the provided string and string format.

The following INSERT would work just fine

INSERT INTO student (`birthDate`) VALUES (STR_TO_DATE('June 10,1997', '%M %d,%Y'));

Because SELECT STR_TO_DATE('June 10,1997', '%M %d,%Y') returns 1997-06-10

The update query you provided would not work if the birthDate field is a date field, because there is no way it could already contain a date in the format m d,Y (you are updating the column values in place)

If you had a column of dates in a varchar column (example, birthDate_mdY) in that format, then you could convert them to a date field like this

UPDATE student SET birthDate = STR_TO_DATE(birthDate_mdY, '%M %d,%Y')

Upvotes: 0

user5922536
user5922536

Reputation:

SQL uses a very specific date format, which is 'YYYY-MM-DD'.

You can insert it as text once it's in that format (E.g no need for STR_TO_DATE).

Example: '1997-06-10'

Upvotes: 5

sourabh singh
sourabh singh

Reputation: 168

INSERT INTO student SET birthDate='1997-06-10'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If you want to insert a date into the column, use a proper date syntax:

INSERT INTO student (`birthDate`)
   VALUES ('1997-06-10');

Upvotes: 2

Related Questions