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

toby's avatar
toby
OP
11 years ago
Level 31

L5 w/ sqlsrv => using Datetime2 fields

Hi everyone,

In a current project, our client is using an IIS 7.5 web server alongside with a MS SQL Server 2008. In all of this tables, every datetime field is a datetime2 field, e.g.

CREATE TABLE [dbo].[user] (
    [id] INT IDENTITY(1,1) NOT NULL,
    [name] VARCHAR(30) NOT NULL,
    [created_at] DATETIME2(7) NOT NULL DEFAULT (getdate()),
    [updated_at] DATETIME2(7) NOT NULL DEFAULT (getdate()),
    [deleted_at] DATETIME2(7) NULL DEFAULT NULL,
    PRIMARY KEY(id)
)

Using Laravel 5, I get the following error as soon as I try to retrieve data via Eloquent:

InvalidArgumentException in Carbon.php line 387:
The format separator does not match
Trailing data

in Carbon.php line 387
at Carbon::createFromFormat('Y-m-d H:i:s.000', '2015-04-20 14:49:51.4130000') in Model.php line 2885
at Model->asDateTime('2015-04-20 14:49:51.4130000') in Model.php line 2390
at Model->attributesToArray() in Model.php line 2369

Is there any way I can use datetime2-fields without hacking the core?

Thanks in advance for your help!!

0 likes
7 replies
toby's avatar
toby
OP
11 years ago
Level 31

Okay, it seems that I have to include this to each model:

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

Or is there an environment variable / config file which I can use to globally define this? If not, I guess it is the easiest way to just use a base model? Any ideas on this approach? Thanks :)

toby's avatar
toby
OP
11 years ago
Level 31

Ok, so there is one open question: How do I use datetime2 fields within a migration (for general fields and for the $table->timestamps(); command)?

uxweb's avatar

@toby There is no way to use that now, by default the SQLServerGrammar for schema generation of laravel uses datetime.

Notice how microsoft recommends the use of datetime2 for new work/development! (https://msdn.microsoft.com/en-us/library/ms187819.aspx), so, i think someone needs to make a pull request to the laravel framework github repository.

Even Brent Ozar, who is a world renowned SQL Server Professional DBA recommends it: http://www.brentozar.com/dates-times-sql-server/

cmnardi's avatar

I'm at the same problem... I create a new config in database.php ... with 'datetimeformat' => 'd/m/Y H:i:s'

Rather than put the getDateFormat() in every model class, I create a abstract Model and every Model will extend this new one This way the format is in the config and all you need to do is extend the new class.

protected function getDateFormat() {
        return config('database.conections.sqlsrv.datetimeformat');
    }
toby's avatar
toby
OP
10 years ago
Level 31

Hi again,

it seems that there is still no update for a datetime2 field... It really is a pitty :( Are there any reasons why it is not supported?

Are there any packages or similar to handle this?

Thanks in advance.

Please or to participate in this conversation.