How format datetime for sql server in laravel?

Question

im trying to format datetime values in laravel to make them match with sql server format, im getting this error:

The separation symbol could not be found Unexpected data found. Unexpected data found. Trailing data

Im using a custom format in my model:

protected $dateFormat = 'd-m-Y H:i:s';

It works good for the create method con controllers, there is no problem, the error happen when try to update and I think is related to how the value is stored in the database.

Using tinker I retrieve the value of updated_at in my model and is shown in the next format:

updated_at: "2018-07-24 09:14:09.000"

So when Im updating this value the format used is:

protected $dateFormat = 'd-m-Y H:i:s';

I think it should be something like d-m-Y H:i:s.000 but doesnt work, how can I solve this?

But...if you're sending the date to SQL Server you'd be better off sending it in yyyy-mm-dd format, not dd-mm-yyyy, so there's no ambiguity. How are you sending the value? Are you passing around strings, or datetime objects? If you're doing this properly, the format should be irrelevant. Date formats are for humans to read, not machines. Internally SQL stores a datetime column in a totally different way, and PHP stores datetime objects differently as well, not in a format you'd immediately find comprehensible. — ADyson
– ADyson, Commented Jul 24, 2018 at 16:02
Yeah, I understand what you are saying, but the default format that carbon uses for datetime values doesnt let even create new records in a table, the fields 'updated_at' and 'created_at' are managed by laravel itself, the only thing I changed was protected $dateFormat = 'd-m-Y H:i:s' in my model — AlexQuezada
– AlexQuezada, Commented Jul 24, 2018 at 16:33
The problem when I try to update a record, probably cause the format has not the last part .000 which sql server stores by default: updated_at: "2018-07-24 09:14:09.000" — AlexQuezada
– AlexQuezada, Commented Jul 24, 2018 at 16:35

dns_nx · Accepted Answer · 2018-10-16 14:53:04Z

5

I had exactly the same issue, when switching to laravel version 5.6. Now I found the correct way to handle this.

Create a Base class like this and add a public method getDateFormat. There you return the date a little bit different

return 'Y-d-m H:i:s.v';

See that date (d) and month (m) are switched and at the end you need to put a .v which stands for milliseconds. This worked for me fine. See here my complete Base class:

<?php

namespace App\Models\Base;

use Illuminate\Database\Eloquent\Model;

class BaseModel extends Model
{
    public function getDateFormat()
    {
        return 'Y-d-m H:i:s.v';
    }

    public $timestamps  = false;
}

answered Oct 16, 2018 at 14:53

dns_nx

3,9556 gold badges47 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mat · Accepted Answer · 2018-08-31 10:43:21Z

2

What version of Laravel are you using? This was fixed in a recent release (can't remember exactly which) that relies on PHP 7.

The dateFormat should be "Y-m-d H:i:s.v". v is Milliseconds (added in PHP 7.0.0). The problem you're experiencing with updates and inserts is caused when the updated/inserted model is returned. It is passed to Carbon to convert date and datetime fields into instances of Carbon, but the datetime fields have 'trailing

answered Aug 31, 2018 at 10:43

Mat

1,6884 gold badges24 silver badges40 bronze badges

Comments

Sahin S. · Accepted Answer · 2020-01-28 11:03:10Z

0

You can also try this one in SQL/Select Query:

\DB::raw ("DATE_FORMAT(comments.date,'%d-%m-%Y %H:%i:%s') as sth_date")

E.g:

Comment::orderBy('date','desc')->with('user')
        ->join('users','comments.id_user', '=', 'users.id')

        ->select('users.username', 'comments.id', 'comments.id_race', 'comments.status', 'comments.ip_address', 'comments.id_user', 
        \DB::raw ("DATE_FORMAT(comments.date,'%d-%m-%Y %H:%i:%s') as sth ") );

answered Jan 28, 2020 at 11:03

Sahin S.

514 bronze badges

Comments

guiCunha · Accepted Answer · 2018-07-24 17:27:40Z

-3

Time fraction is a configuration of your database, you can see it below, so you can just try to switch on your database.

https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

But using something like $d->format("Y-m-d H:i:s.u") you can format/manipulate using class Datetime().

answered Jul 24, 2018 at 17:27

guiCunha

3931 silver badge4 bronze badges

2 Comments

AlexQuezada Over a year ago

still not working, not sure what should I do, and just happen while editing/updating records

dns_nx Over a year ago

OP is using Micrsoft SQL Server and not MySQL

Collectives™ on Stack Overflow

How format datetime for sql server in laravel?

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related