CODEWITHSUNDEEP
phpmysqllaravel
goateee25
goateee25

Reputation: 183

Invalid datetime format: 1292 Incorrect datetime value - Laravel 5.2

I'm getting this error when I insert these value into my database table:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value:
'24/06/2017' for column 'registration_from' at row 1 
(SQL: insert into `campus_registrations` (`campus_major_class_id`, 
`registration_from`, `registration_to`, `testing`, `announcement`, 
`enrollment_from`, `enrollment_to`, `updated_at`, `created_at`) values (3, 
24/06/2017, 27/06/2017, 13/07/2017, 01/08/2017, 05/09/2017, 31/01/2018,
 2017-06-07 09:39:31, 2017-06-07 09:39:31))

Do I have to intiate the datetime first or what?

Upvotes: 12

Views: 105005

Answers (11)

Nigus Abate
Nigus Abate

Reputation: 11

I had the same problem importing Excel data, but I finally solved the problem. This happens because my Excel language is English (US) and the default database format is Y-M-D, which means English (UK). So, you should change your language to English. (UK) That is fine work.

Upvotes: 0

Maizied Hasan Majumder
Maizied Hasan Majumder

Reputation: 1053

Easiest way to insert date time.

$item->date= date('Y-m-d H:i:s', strtotime($request->date));

Upvotes: 0

shasi kanth
shasi kanth

Reputation: 7094

If the column has default set to NULL, you can probably do something like this in your code:

$model->column = NULL;

Upvotes: -2

CodeToLife
CodeToLife

Reputation: 4151

  1. I've dropped problem column in new migration:

$table->dropColumn('time_to_deliver');

  1. created new migration file and now instead of 'timestamp' put in 'time' as the type:

$table->time('time_to_deliver')->nullable()->after('date_to_deliver');

  1. in next migration file have done next(within a loop):

$orderLcl = new Order;// a Model

$orderLcl->time_to_deliver = date("H:i",$mock_timestampLcl);

.....
$orderLcl->save();

Upvotes: 0

Rytis Dereskevicius
Rytis Dereskevicius

Reputation: 1471

Don't forget about Y2K38(Year 2038 problem) -

timestamp has a limit of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (source), whilst dateTime has a range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59, so consider what you plan on storing in that field before determining which type to use.

timestamp and dateTime are similar - they store a date (YYYY-MM-DD) and time (HH:MM:SS) together in a single field i.e. YYYY-MM-DD HH:MM:SS. The difference between the two is that timestamp can use (MySQL's) CURRENT_TIMESTAMP as its value, whenever the database record is updated. This can be handled at the database level and is great for Laravel's created_at and updated_at fields.

date stores just the date component i.e. YYYY-MM-DD (1000-00-01 to 9999-12-31).

timestamps doesn't take an argument, it's a shortcut to add the created_at and updated_at timestamp fields to your database.

So setting a date on timestamp bigger than 2038-01-19 03:14:07 UTC will also throw - SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value

Upvotes: 15

Kwaye Kant
Kwaye Kant

Reputation: 57

This was happening to me under laravel 5.6, until I changed the code to $table->date('last_date')->default(now());

Upvotes: 1

Ryan Dhungel
Ryan Dhungel

Reputation: 3775

I got the same error while trying to register user from the vue js frontend to laravel backend API.

The solution was updating the mysql strict mode to false. If anyone knows the cons of this approach, please leave your comments!

//config/database.php
'mysql' => [
    'strict' => false,
]

Upvotes: 32

Ganesh Khadka
Ganesh Khadka

Reputation: 87

I got the same problem and finally i solved the problem.Yoou can add the few line of code at the end of the add.blade.php

 $('.date-picker').datepicker({
                format: 'yy/mm/dd',
                autoclose: true,
                todayHighlight: true
            });

Upvotes: 1

Alexey Mezenin
Alexey Mezenin

Reputation: 163778

Since you're not using standard datetime format, define a mutator for each date. For example:

public function setRegistrationFromAttribute($value)
{
    $this->attributes['registration_from'] =  Carbon::parse($value);
}

Upvotes: 15

Saumini Navaratnam
Saumini Navaratnam

Reputation: 8850

Reference

MYSQL recognises date value in either YYYY-MM-DD or YY-MM-DD format. You can use /. This 24/06/2017 considered invalid. Try 2017/06/24

Upvotes: 0

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

The error is here:

Incorrect datetime value: '24/06/2017' for column 'registration_from' at row 1

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.

Upvotes: 15

Related Questions