Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

faridsilva's avatar

Carbon date conversión failing just in one date field among others

I'm facing a strange situation on date conversion using carbon on laravel 5.5 app. I have several date fields defined as date in mysql with NULL as default. The mysql format is 'Y-m-d' but in front-end forms I must show 'd/m/Y' format.

To achieve that I'm using carbon to convert dates through model mutators. It means that, first I have dates as:

protected $dates = [
'valid_from',
'valid_to',
'provisional_reception',
'final_recepcion',
'policy_cancellation',
];

My mutators are in the following way:

public function setValidFromAttribute($input)
{
    if ($input != null && $input != '') {
        $this->attributes['valid_from'] = Carbon::createFromFormat('d/m/Y', $input)->format('Y-m-d');
    } else {
        $this->attributes['valid_from'] = null;
    }
}


public function getValidFromAttribute($input)
{
    if ($input != null) {
        return Carbon::createFromFormat('Y-m-d', $input)->format('d/m/Y');
    } else {
        return '';
    }
}


public function setValidToAttribute($input)
{
    if ($input != null && $input != '') {
        $this->attributes['valid_to'] = Carbon::createFromFormat('d/m/Y', $input)->format('Y-m-d');
    } else {
        $this->attributes['valid_to'] = null;
    }
}


public function getValidToAttribute($input)
{
    if ($input != null) {
        return Carbon::createFromFormat('Y-m-d', $input)->format('d/m/Y');
    } else {
        return '';
    }
}

public function setProvisionalReceptionAttribute($input)
{
    if ($input != null && $input != '') {
        $this->attributes['provisional_reception'] = Carbon::createFromFormat('d/m/Y', $input)->format('Y-m-d');
    } else {
        $this->attributes['provisional_reception'] = null;
    }
}

public function getProvisionalReceptionAttribute($input)
{
    if ( $input != null) {
        return Carbon::createFromFormat('Y-m-d', $input)->format('d/m/Y');
    } else {
        return '';
    }
}  
public function setFinalReceptionAttribute($input)
{
    if ($input != null && $input != '') {
        $this->attributes['final_reception'] = Carbon::createFromFormat('d/m/Y', $input)->format('Y-m-d');
    } else {
        $this->attributes['final_reception'] = null;
    }
}


public function getFinalReceptionAttribute($input)
{

    if ($input != null) {
        return Carbon::createFromFormat('Y-m-d', $input)->format('d/m/Y');
    } else {
        return '';
    }
} 


public function setPolicyCancellationAttribute($input)
{
    if ($input != null && $input != '') {
        $this->attributes['policy_cancellation'] = Carbon::createFromFormat('d/m/Y', $input)->format('Y-m-d');
    } else {
        $this->attributes['policy_cancellation'] = null;
    }
}


public function getPolicyCancellationAttribute($input)
{
    if ($input != null) {
        return Carbon::createFromFormat('Y-m-d', $input)->format('d/m/Y');
    } else {
        return '';
    }
}

Now, everything is working fine except for one field: "policy_cancellation"

When insert data from forms to database, if policy_cancellation is empty, submission succeed, but if it has a data, PDO returns an error, e.g.:

valid_from  "01/01/2019"
valid_to "30/10/2019"
provisional_reception ""
final_recepcion ""
policy_cancellation "25/07/2019"
fires the following:

[2019-07-28 15:33:17] local.ERROR: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '25/07/2019' for column `database`.`insurance_policies_emitteds`.`policy_cancellation` at row 10586 (SQL: update `insurance_policies_emitteds` set `final_amount` = , `policy_cancellation` = 25/07/2019 where `policy_number` = 1234567889) {"userId":1,"email":"[email protected]","exception":"[object] (Illuminate\Database\QueryException(code: 22007): SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '25/07/2019' for column `database`.`insurance_policies_emitteds`.`policy_cancellation` at row 10586 (SQL: update `insurance_policies_emitteds` set `final_amount` = , `policy_cancellation` = 25/07/2019 where `policy_number` = 1234567889) at /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: 22007): SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '25/07/2019' for column `database`.`insurance_policies_emitteds`.`policy_cancellation` at row 10586 at /Volumes/data/Sites/database/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119, PDOException(code: 22007): SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '25/07/2019' for column `database`.`insurance_policies_emitteds`.`policy_cancellation` at row 10586 at /Volumes/data/Sites/database/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117)
[stacktrace]
#0 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('update `insuran...', Array, Object(Closure))
#1 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Connection.php(490): Illuminate\Database\Connection->run('update `insuran...', Array, Object(Closure))
#2 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Connection.php(423): Illuminate\Database\Connection->affectingStatement('update `insuran...', Array)
#3 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2173): Illuminate\Database\Connection->update('update `insuran...', Array)
#4 /Volumes/data/Sites/database/app/Observers/InsurancePoliciesEmittedObserver.php(16): Illuminate\Database\Query\Builder->update(Array)
#5 [internal function]: App\Observers\InsurancePoliciesEmittedObserver->saved(Object(App\Models\InsurancePoliciesEmitted))
#6 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php(369): call_user_func_array(Array, Array)
#7 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php(200): Illuminate\Events\Dispatcher->Illuminate\Events\{closure}('eloquent.saved:...', Array)
#8 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php(173): Illuminate\Events\Dispatcher->dispatch('eloquent.saved:...', Array, false)
#9 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Concerns/HasEvents.php(148): Illuminate\Events\Dispatcher->fire('eloquent.saved:...', Object(App\Models\InsurancePoliciesEmitted))
#10 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(591): Illuminate\Database\Eloquent\Model->fireModelEvent('saved', false)
#11 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(562): Illuminate\Database\Eloquent\Model->finishSave(Array)
#12 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(755): Illuminate\Database\Eloquent\Model->save()
#13 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Support/helpers.php(1038): Illuminate\Database\Eloquent\Builder->Illuminate\Database\Eloquent\{closure}(Object(App\Models\InsurancePoliciesEmitted))
#14 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(756): tap(Object(App\Models\InsurancePoliciesEmitted), Object(Closure))
#15 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1477): Illuminate\Database\Eloquent\Builder->create(Array)
#16 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1489): Illuminate\Database\Eloquent\Model->__call('create', Array)
#17 /Volumes/data/Sites/database/app/Http/Controllers/Admin/InsurancePoliciesEmittedsController.php(63): Illuminate\Database\Eloquent\Model::__callStatic('create', Array)
#18 [internal function]: App\Http\Controllers\Admin\InsurancePoliciesEmittedsController->store(Object(App\Http\Requests\Admin\StoreInsurancePoliciesEmittedsRequest))
#19 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(54): call_user_func_array(Array, Array)
#20 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(45): Illuminate\Routing\Controller->callAction('store', Array)
#21 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Route.php(212): Illuminate\Routing\ControllerDispatcher->dispatch(Object(Illuminate\Routing\Route), Object(App\Http\Controllers\Admin\InsurancePoliciesEmittedsController), 'store')
#22 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Route.php(169): Illuminate\Routing\Route->runController()
#23 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Router.php(658): Illuminate\Routing\Route->run()
#24 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\Routing\Router->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#25 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(41): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#26 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Routing\Middleware\SubstituteBindings->handle(Object(Illuminate\Http\Request), Object(Closure))
#27 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#28 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php(43): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#29 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Auth\Middleware\Authenticate->handle(Object(Illuminate\Http\Request), Object(Closure))
#30 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#31 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php(68): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#32 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Foundation\Http\Middleware\VerifyCsrfToken->handle(Object(Illuminate\Http\Request), Object(Closure))
#33 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#34 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#35 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\View\Middleware\ShareErrorsFromSession->handle(Object(Illuminate\Http\Request), Object(Closure))
#36 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#37 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(63): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#38 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Session\Middleware\StartSession->handle(Object(Illuminate\Http\Request), Object(Closure))
#39 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#40 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#41 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle(Object(Illuminate\Http\Request), Object(Closure))
#42 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#43 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(66): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#44 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Cookie\Middleware\EncryptCookies->handle(Object(Illuminate\Http\Request), Object(Closure))
#45 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#46 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(102): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#47 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Router.php(660): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#48 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Router.php(635): Illuminate\Routing\Router->runRouteWithinStack(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request))
#49 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Router.php(601): Illuminate\Routing\Router->runRoute(Object(Illuminate\Http\Request), Object(Illuminate\Routing\Route))
#50 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Router.php(590): Illuminate\Routing\Router->dispatchToRoute(Object(Illuminate\Http\Request))
#51 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(176): Illuminate\Routing\Router->dispatch(Object(Illuminate\Http\Request))
#52 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}(Object(Illuminate\Http\Request))
#53 /Volumes/data/Sites/database/vendor/fideloper/proxy/src/TrustProxies.php(56): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#54 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Fideloper\Proxy\TrustProxies->handle(Object(Illuminate\Http\Request), Object(Closure))
#55 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#56 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(30): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#57 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(Object(Illuminate\Http\Request), Object(Closure))
#58 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#59 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(30): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#60 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(Object(Illuminate\Http\Request), Object(Closure))
#61 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#62 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#63 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Foundation\Http\Middleware\ValidatePostSize->handle(Object(Illuminate\Http\Request), Object(Closure))
#64 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#65 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(46): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#66 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode->handle(Object(Illuminate\Http\Request), Object(Closure))
#67 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#68 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(102): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#69 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(151): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#70 /Volumes/data/Sites/database/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(116): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter(Object(Illuminate\Http\Request))
#71 /Volumes/data/Sites/database/public/index.php(55): Illuminate\Foundation\Http\Kernel->handle(Object(Illuminate\Http\Request))
#72 /Volumes/data/.composer/vendor/laravel/valet/server.php(158): require('/Volumes/data/S...')
#73 {main}
"}

Another strange situation (for me at least) is that inserting a break in policy_cancellation mutator, it shows a correct formated value

public function setPolicyCancellationAttribute($input)
{
    if ($input != null && $input != '') {
       $this->attributes['policy_cancellation'] = \Carbon\Carbon::createFromFormat('d/m/Y', $input)->format('Y-m-d');
     dd($this->attributes['policy_cancellation']);  
   } else {
       $this->attributes['policy_cancellation'] = null;
   }
}

it returns "2019-07-25", which should be correct date conversion. But again, removing dd() results in the previous error.

I've tried to omit carbon in this particular field, using plain php:

public function setPolicyCancellationAttribute($input)
    {
         if ($input != null && $input != '') {
            $input = str_replace('/', '-', $input); // convert from d/m/Y to d-m-Y for disambiguation
            $input = strtotime($input);
            $input = date('Y-m-d',$input);
            $this->attributes['policy_cancellation'] = $input;
            //$this->attributes['policy_cancellation'] = \Carbon\Carbon::createFromFormat('d/m/Y', $input)->format('Y-m-d');
            //dd($this->attributes['policy_cancellation']);  
        } else {
            $this->attributes['policy_cancellation'] = null;
        }
    }

But there is no difference. dd() show a correct formatted value of "2019-07-25", and then mysql fires the same error previously described.

I'm stuck on this situation without find any alternative to solve the error, so any idea will be truly appreciated.

0 likes
2 replies
mstrauss's avatar

Can you show the method which is used to insert the data into the DB?

faridsilva's avatar

Yes, of course

public function store(StoreInsurancePoliciesEmittedsRequest $request)
    {
        if (! Gate::allows('insurance_policies_emitted_create')) {            
        }
        $request = $this->saveFiles($request);
        $insurance_policies_emitted = InsurancePoliciesEmitted::create($request->all());
        return redirect()->route('admin.insurance_policies_emitteds.index');
    }

public function update(UpdateInsurancePoliciesEmittedsRequest $request, $id)
    {
        $request = $this->saveFiles($request);
        $insurance_policies_emitted = InsurancePoliciesEmitted::findOrFail($id);
        $insurance_policies_emitted->update($request->all());
        return redirect()->route('admin.insurance_policies_emitteds.index');
    }

But remember that as I said at the first, there are several date fields and just "policy_cancellation" is the one that fails.

Please or to participate in this conversation.