Laravel allowing null dates on Model::create() but not Model->update()

Question

I have setup my Laravel migration to allow nullable dates for my StartTime and EndTime entries as such:

$table->dateTime( 'StartTime' )->nullable();
$table->dateTime( 'EndTime' )->nullable();

When I create a new entry through eloquent, it allows me to insert null values into my database successfully:

try {

    // Create the new Campaign record
    $campaign = Campaign::create( $request->all() );

}


+----+-------+--------+-----------+---------+---------------------+
| Id | Name  | Active | StartTime | EndTime | created_at          |
+----+-------+--------+-----------+---------+---------------------+
|  1 | Test2 |      0 | NULL      | NULL    | 2020-07-02 22:01:22 |
+----+-------+--------+-----------+---------+---------------------+
1 row in set (0.00 sec)

However, when I later try and update my record using eloquent and still passing a null value for StartTime, it throws an error:

try {

    // Get a reference to the campaign
    $campaign = Campaign::find( $id );
            
    // Update the campaign
    $campaign->update( $request->all() );

}


(22007) SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: 'null' for column 'StartTime' at row 1

In the case of the create method, I am not passing in a StartTime value at all, but in the case of the update method, I am simply passing back the null value that Laravel returns as part of the model. So in other words, I haven't altered the value of StartTime at all, I've simply just passed $campaign back to Laravel for the update.

So it seems that Laravel is assigning the nullable() upon insert of a new entry into the database, but will not allow me to pass a null value back for the update.

Am I missing something here? I can't seem to find a solution to this anywhere.

UPDATE

Okay, so further investigation seems like my problem is stemming from the AngularJS $http POST request. For troubleshooting purposes, I added code to my Laravel controller to alter the StartTime to null:

if( $request->StartTime === 'null' ) {

   $request['StartTime'] = null;

}

And that worked. So it looks like Angular is passing the null value back in the request as 'null'

Show the code you're using to update your record. The error message suggests a string `'null'` vs `null`, but since you didn't post any code, that's simply a guess. — Tim Lewis, Jul 02 '20 at 13:29

score 0 · Answer 1 · answered Jul 02 '20 at 16:14

0

Laravel 5.3 was updated to use MySQL "strict" mode by default, which includes the NO_ZERO_DATE mode.

The issue is that your existing data was allowed to have '0000-00-00 00:00:00' as a datetime value. But, now your connection is using a sql mode that does not allow that value (NO_ZERO_DATE).

However, the quick option is to just disable "strict" mode on your database connection. Open your config/database.php file, and make sure your database connection shows 'strict' => false.

Or, create migration like this :

$table->datetime('StartTime')->nullable($value = true);

Or,

$table->datetime('StartTime')->default(null);

answered Jul 02 '20 at 16:14

STA

30,729
8
45
59

I tried changing the `strict` mode to `false` and that allowed it, however, it wrote the entries into my database as '0000-00-00 00:00:00' instead of `null`. I don't understand how it allows the null on a create method but not the update. – Riples Jul 02 '20 at 21:44
This is mostly a MySql issue, as zero dates are disabled for newer versions of MySql. In addition, the NO_ZERO_DATE mode was deprecated (see: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_date). To get around this, you could change all zero dates in your database to the unix epoch time 1970-01-01 00:00:00 (which is what I had to do with mine after upgrading MySql). – STA Jul 03 '20 at 05:51

Laravel allowing null dates on Model::create() but not Model->update()

1 Answers1