Carbon in Laravel 4 InvalidArgumentException - Unexpected data found. Trailing data

Question

I'm trying to get an Eloquent query result for DB::raw("DATE_FORMAT(created_at, '%m-%d-%Y %r') AS created_at") but each time I get this exception from Carbon:

InvalidArgumentException
Unexpected data found. Trailing data

If I change it to just created_at instead of employing MySQL's DATE_FORMAT() function, then it gets the data without issue.

I've not only done this sort of date formatting without issue before, but I checked every field in the database table (there are only 10 for this seed) and each is a standard valid date, so I'm wondering why Carbon is pitching a fit.

Running this in Laravel 4.1.

Answer 1

In an Eloquent query result (model) every date field is a carbon object, it means, if you query a model which contains any timestamp field like created_at, updated_at (basically created using timestamps() during migration) and deleted_at, Laravel converts them to a Carbon object and you may use any public methods of Carbon, for example:

$user = User::find(1);

// '2014-04-20 19:02:09' will become 'Apr 20, 2014'
$user->created_at->toFormattedDateString();

So, you may directly use any public method of Carbon on a timestamp field available in a model. If you try this:

dd($user->created_at);

Then the output will be:

object(Carbon\Carbon)[456]
  public 'date' => string '2014-04-20 19:02:09' (length=19)
  public 'timezone_type' => int 3
  public 'timezone' => string 'UTC' (length=3)

So, if you want to format a date, you may use:

// outputs like: 'Sunday 20th of April 2014 07:02:09 PM'
$user->created_at->format('l jS \\of F Y h:i:s A')

Update:

If you want to change this behavior, means that, if you want tell Laravel that, which fields should be converted automatically to Carbon object then you may override that by creating a method in your model like:

public function getDates()
{
    // only this field will be converted to Carbon
    return array('updated_at');
}

To totally disable date mutations, simply return an empty array from the getDates method. For more details, check Date Mutators on Laravel website.

Answer 2

I realise the original question refers to MySQL but I had the same error with MSSQL. The problem turned out to be that MSSQL's datetime column type has a precision of .001 seconds but I was setting my model's format to no precision:

protected function getDateFormat()
{
    return 'Y-m-d G:i:s';
}

By using the newer DateTime2 column type and turning off the precision, I fixed the error. I.e.

datetime2(0)

You could instead change the format in getDateFormat, of course.