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

Question

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":"faridsilva@gmail.com","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 lest) 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.

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

Answers (1)

Related Questions

Carbon date conversi&#243;n failing just in one date field among others

Answers (1)

Related Questions

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