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

ERROR 1411 in MySQL: Incorrect datetime value

I am trying to insert dates along with other data into my SQL table:

insert into weather values
(1, 'London', -1, str_to_date('2021-01-01','y%-m%-d%')),
(2, 'London', -2, str_to_date('2021-01-02','y%-m%-d%')),
(3, 'London', 4, str_to_date('2021-01-03','y%-m%-d%')),

but I am getting the message below:

Error Code: 1411. Incorrect datetime value:

‘2021-01-01’ for function str_to_date

I’ve tried different combinations of dates and looked online but couldn’t find one because the format looks alright. Even MySQL documentation has the same syntax.

Tags:

2

Answers


  1. 0

    The correct format is : '%Y-%m-%d'

    %y is for 2 digits year format, is working for str_to_date('21-01-01','%y-%m-%d')

    So your query should be :

    insert into weather values
(1, 'London', -1, str_to_date('2021-01-01','%Y-%m-%d')),
(2, 'London', -2, str_to_date('2021-01-02','%Y-%m-%d')),
(3, 'London', 4, str_to_date('2021-01-03','%Y-%m-%d'));

    Docs here

    Login or Signup to reply.
  2. 0

    By now you should know that you had a typo inside your format, swapping the letter with the ‘%’ symbol (‘y%-m%-d%’ instead of ‘%Y-%m-%d’).

    Although since you’re respecting the standard format in your date string, you can avoid the STR_TO_DATE pattern matching operation and apply a simple CAST instead.

    INSERT INTO weather VALUES
(1, 'London', -1, CAST('2021-01-01' AS DATE)),
(2, 'London', -2, CAST('2021-01-02' AS DATE)),
(3, 'London', 4, CAST('2021-01-03' AS DATE));

    Output:

    id place val date_
    1 London -1 2021-01-01
    2 London -2 2021-01-02
    3 London 4 2021-01-03

    Check the demo here.

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

Back To Top
Search