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

jotero32's avatar

Date saves to database as 0000-00-00 00:00:00

Hello there, I hope someone can help. Ive been researching for sometime now on how to properly save from a form to the database when a user choose a specific date. On the create page it looks as such:

create.blade.php

{!! Form::label('published_date', 'Published Date', ['class' => 'control-label']) !!}
                            {!! Form::input('date', 'published_date', date('Y-m-d'), ['class' => 'form-control']) !!}

Controller (Store)

    {
        $binding        =       Binding::with('id');
        $categories     =       Categories::with('id');
        $conditions     =       Condition::with('id');
        $editions       =       Edition::with('id');
        $publishers     =       Publisher::with('id');
        $rarities       =       Rarity::with('id');
        $signatures     =       Signature::with('id');

        // Availability option switch (on = 1; off = 0)
        $available  =   Input::all();

        // Convert added_date format to be compatible with database ISO 8601 format
        $added = Input::all();
        $added['added_date'] = date('Y-m-d', strtotime($added['added_date']));

        // Convert published_date format to be compatible with database ISO 8601 format
        $published = Input::all();
        $published['published_date'] = date('Y-m-d', strtotime($published['published_date']));

        $book = new Book($request->all());
        Auth::user()->books()->
            save(
            $book,
            $binding,
            $categories,
            $conditions,
            $editions,
            $publishers,
            $rarities,
            $signatures,
            $added,
            $published,
            $available);


        return redirect('admin/library');

Controller (Show)

    public function show($id)
    {
        $books = DB::table('books')->where('books.id', '=', $id)
                ->join('bindings', 'books.binding_id', '=', 'bindings.id')
                ->join('categories', 'books.category_id', '=', 'categories.id')
                ->join('conditions', 'books.condition_id', '=', 'conditions.id')
                ->join('editions', 'books.edition_id', '=', 'editions.id')
                ->join('publishers', 'books.publisher_id', '=', 'publishers.id')
                ->join('rarities', 'books.rarity_id', '=', 'rarities.id')
                ->join('signatures', 'books.signature_id', '=', 'signatures.id')
                ->join('users', 'books.user_id', '=', 'users.id')
                ->first();
        
        return view('admin.library.show')
            ->with('books', $books);
    }

Model

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;

class Book extends Model {

    /**
     * @var string
     */
    protected $primaryKey = 'id';

    /**
     * Allowed Fields for mass Input
     *
     * @var array
     */
    protected $fillable = [

        'title',
        'author',
        'sec_author',
        'thi_author',
        'for_author',
        'availability',
        'barcode',
        'isbn',
        'dewey',
        'universal',
        'loc',
        'added_date',
        'binding_id',
        'category_id',
        'condition_id',
        'edition_id',
        'publisher_id',
        'rarity_id',
        'signature_id'

    ];

    /**
     * @var array
     */
    protected $dates = ['added_date', 'published_date'];

    /**
     * @return array
     */
    public function getDates()
    {
        // Return any columns that are date or datetime types
        // to automatically have Laravel convert them to Carbon
        // objects. (Also include created_at, updated_at and
        // deleted_at if you use them)
        return ['added_date', 'published_date'];
    }

    /**
     * @param $date
     */
    public function setAddedDateAttribute($date){

        $this->attributes['added_date'] = Carbon::createFromFormat('Y-m-d', $date);
    }

    /**
     * @param $date
     */
    public function setPublishedDateAttribute($date){

        $this->attributes['published_date'] = Carbon::toFormattedDateString('Y-m-d', $date);
    }

I have tried a bunch of different methods from what I have read and saw, but honestly I cant believe it can be this complex for a user to simply choose from a datetime picker in the form and save it. My frustration level is high :( A big thank you to everyone in advance.

0 likes
9 replies
zachleigh's avatar

So you want to save the date without the time, in Y-m-d format? What does your table migration look like?

jekinney's avatar
Level 47

@jotero32

By setting the $dates in your model it calls Carbon to create the time stamp already. so technically a set attribute isn't required.

In your View if your input is type date if formats the date already for you.

To return the proper format for display in the input type date (as it will not show by default)

public function getPublishedAtAttribute($published_at)
    {
        return $this->attributes['publish_at'] = Carbon::parse($publish_at)->format('Y-m-d');
    }

Also notice the $publish_at not $date. Eloquent has no idea what $date is unless you have a date column in your table.

2 likes
jotero32's avatar

Hey Zach,

Here is the migration:

public function up()
    {
        Schema::table('books', function(Blueprint $table)
        {
            $table->timestamp('published_date')->nullable();
            $table->timestamp('added_date');
        });
    }

Honestly all I want is the user to be able to choose from a date picker the date the book was published, the added_date is the date it was saved in-to inventory (this works fine).

jotero32's avatar

jekinney,

It makes sense to me now. Honestly, i was completely lost with that, now I understand. I appreciate your assistance and also yours zachleigh. I hope this helps anyone in the future.

jekinney's avatar

@jotero32

Awhile ago I spent a huge amount of time with a similar issue trying to out put a date and had a carbon string error... So I am glad to help you keep your hair :).

1 like
jekinney's avatar

@zachallia in a time stamp migration it sets the default to all 0's so if you don't pass a time it will fill in the rest a 0's for time, which is easier for Carbon from my experience.

Revolution's avatar

Thanks to those in this thread.

Here's my solution; seems to work so far. My tables have the '0000-00-00' null value, and I don't want to go through the hastle of trying to set new defaults. There are a ton of people who seem to have this issue. I couldn't get a ternery operator check to work properly from the view level. What did work was performing the logical check at the model level and then passing a string value. Similar to the selected answer above. No idea if my approach below is flawed for some reason!

/****** Getter on Dates*****/
public function getDateOfDeathAttribute($date_of_death)
{
    if ($this->attributes['date_of_death'] == '0000-00-00')
        return 'n/a';
    else
        return $this->attributes['date_of_death'] = Carbon::parse($date_of_death)->format('m/d/Y');
}

My model is storing '0000-00-00' in date_of_death column. From the view, no logical checks were working. The getter method worked. Thanks to those in this thread.

whfamily2006's avatar

@jekinney if my date stored with time too and at the time of querying records i want to apply dateformat 'YYYY-MM-DD', how can i query the record...

$today = DATE_FORMAT(now(),'%Y-%m-%d');
$records = Attendnace::where('DATE_FORMAT(attendance_date,'%Y-%m-%d')', $today)->get();

and DATE_FORMAT not working here...

and if i create getAttendanceDateAttribute($value) for get specific format than its change every where... and eloquent return in this format 'YYYY-MM-DD', but i also want to use time somewhere ... please help me...

Please or to participate in this conversation.