Carbon::createFromFormat() return a not valid datetime format for laravel date validation

Hey, you can actually chose the format you want to validate. Here are the docs: https://laravel.com/docs/6.x/validation#rule-date-format For example:

$rule['birthday'] = 'required|date_format:d/m/Y';

Ah how stupid of me, I have tried to use that validation before, but i using it wrong, the validation I use is Y-m-d because I thought I need to follow the dump result from the Carbon::createFromFormat('d/m/Y', $request->birthday) format that has a Y-m-d result.

Thanks for the enlightment @manelgavalda

Now after it pass the validation, it hits another error SQLSTATE[22008]: Datetime field overflow: 7 ERROR: date/time field value out of range: "20/11/2019" HINT: Perhaps you need a different "datestyle" setting.

The problem is caused because the date you are trying to save is not in the standard DATETIME format. You should format your date according to the format you have in the database.

You have many options here, I think the easier will be to show us the method you are using to validate and save the date to the database, to see the easier way to do this.

This is my controller

public function store(User $user, Request $request) {
    // if I dump the request->birthday now it will return "20/11/2019"

    $request->birthday = Carbon::createFromFormat('d/m/Y', $request->birthday);

    // if I dump $request->birthday now will return carbon datetime.

    $user->profile()->store($request->validate([
        'birthday' => 'sometimes|nullable|date_format:d/m/Y'
    ]);

    return back();
}

and I get an error

SQLSTATE[22008]: Datetime field overflow: 7 ERROR: date/time field value out of range: "20/11/2019" HINT: Perhaps you need a different "datestyle" setting.

shouldn't the data that submitted already a carbon datetime after this line?

$request->birthday = Carbon::createFromFormat('d/m/Y', $request->birthday);

I have tried this also

$request->birthday = Carbon::createFromFormat('d/m/Y', $request->birthday)->toDateString();

and then it will goes back and hit the validation error

This birthday field is not a valid date d/m/Y

My table schema is below

Schema::create('user_profiles', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedInteger('user_id');
    $table->date('birthday')->nullable();

    $table->foreign('user_id')->references('id')->on('users');
});

I think that $request->validate is not returning the changed birthday date.

In your case I would validate the data in the form format first and then parse it to carbon format in order to save it.

public function store(User $user, Request $request) {
   $validatedData = $request->validate([
        'birthday' => 'sometimes|nullable|date_format:d/m/Y'
    ]);

    $validatedData['birthday'] = Carbon::createFromFormat('d/m/Y', $request->birthday);

    $user->profile()->store($validatedData);

    return back();
}

I think something like this should work.

Yeah, it works.. thanks for the help @manelgavalda

Just curious about why when i dump the $request->birthday = Carbon::createFromFormat('d/m/Y', $request->birthday) it is already a carbon datetime format, but it return the date field overflow error

The thing when you are doing this:

    $request->birthday = Carbon::createFromFormat('d/m/Y', $request->birthday);

You are just modifying the $request->birthday (or $request['birthday']). And then you call:

$request->validate([
   'birthday' => 'sometimes|nullable|date_format:d/m/Y'
])

The birthday here it's coming from $request->validate([...])['birthday'].

So this birthday is not the same birthday you changed before so it's not the Carbon parsed date you think it is.

make sense, thanks for the enlightenment @manelgavalda