Invalid datetime format: 1292 When using old dates

Question

I have people table and in this I have a field named birthday as timestamp.

When i insert date data like: 2017-05-19 its ok and no errors but when i want to insert some old dates like 1959-08-12 its says:

Invalid datetime format: 1292 Incorrect datetime value: '1959-08-12' for column 'birthday' at row 1

I am inserting the date like this:

Person::create([
...
  'birthday'   => date('Y-m-d' , strtotime($birthday)),
...
])

Also i try this question/answer and remove the NO_ZERO_IN_DATE,NO_ZERO_DATE from sql_mode variable, but not working for me.

MySQL: 5.7.23 PHP: 7.2 Laravel: 6.5

Make sure the timezone on the server and MySQL is the same. If MySQL is not on the correct timezone you can update it with SET GLOBAL time_zone = timezone; — VIKAS KATARIYA, Nov 14 '19 at 09:11

Salman A · Accepted Answer · 2019-11-14T09:11:24.990

I am guessing that the birthday column is of datatype TIMESTAMP which has a range of 1970-01-01 00:00:01 to 2038-01-19 03:14:07, UTC.

create table t(birthday timestamp);
insert into t values ('1970-01-02');
/* Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.094 sec. */
insert into t values ('1969-12-30');
/* SQL Error (1292): Incorrect datetime value: '1969-12-30' for column 'birthday' at row 1 */

I would suggest using DATE datatype for birthdays because it does not contain time portion and has a larger range 1000-01-01 to 9999-12-31.

score 0 · Answer 2 · answered Nov 14 '19 at 09:14

the default format for date column in mysql is Y-m-d and for datetime is Y-m-d H:i:s. So change your date to this format and try again.

Or

Try this, parse the date using a Carbon object.

Person::create([
...
  'birthday'   => Carbon::parse($birthday),
...
])

Invalid datetime format: 1292 When using old dates

2 Answers2