使用Laravel的pdo_sqlsrv和日期格式

Question

I wanted to try Laravel for a while, so I spent the afternoon setting it up.

On Windows I installed: WAMP 32-bit with PHP 5.4, SQL Server 3.0 drivers from Microsoft (that's why I use WAMP 32 bit) and Composer.

Then I created a project, download Laravel and generators. I created a scaffold to see the kind of code it generates and test it.

My first "create" action failed because of a nvarchar to datetime conversion error. I pasted the query in SQL Server Management Studio (SSMS) and fire it with same results.

The mistake comes from the date format. Laravel put it this way: "2013-12-17 16:55:124". But before the request, it sends some set to SQL Server, and one is very interesting:

set dateformat dmy

To me set dateformat dmy means it expects a date like this: "17-12-2013 16:55:124"

In SSMS changing the set dateformat to:

set dateformat ymd

or changing the date format in the request makes it works.

I don't think it is a bug. I just think something is misconfigured. I just don't know what.

Answer 1

I finally found a way around which seems quite clean. The trick is to force Laravel to use datetime2 instead of datetime with SQL Server.

In order to do this you need to do those changes:

In Illuminate/Database/Schema/Grammars/SqlServerGrammar.php

protected function typeDateTime(Fluent $column)
{
    return 'datetime'; // Change to datetime2
}

And

protected function typeTimestamp(Fluent $column)
{
    return 'datetime'; // Change to datetime2
}

Then in Illuminate/Database/Query/Grammars/SqlServerGrammar.php:

public function getDateFormat()
{
    return 'Y-m-d H:i:s.000'; // change to 'Y-m-d H:i:s.0000000'
}

I hope it helps someone...