Problem with SQL DateTime when using pdo_sqlsrv

[ownmaster]

Environment:

• Laravel Version: 5.5
• PHP Version: 7.0+
• MS SQL 2008 R2
• pdo_sqlsrv 4.3.0 and 5.1.1-preview
• Ubuntu 16.04.3 LTS

Description:

I have a problem parsing DateTime by Eloquent when using pdo_sqlsrv PHP extension.
It was detected on fields generated by the timestamps() method, in SQL database their type is datetime.
Exception (InvalidArgumentException: Data missing) happens in vendor\laravel\framework\src\Illuminate\Database\Eloquent\Concerns\HasAttributes.php, function asDateTime($value), return Carbon::createFromFormat($this->getDateFormat(), $value);
Reason - mismatch between expected and actual DateTime format: getDateFormat() expects "Y-m-d H:i:s.000" but $value="2017-10-27 17:10:21" ("Y-m-d H:i:s") - milliseconds are omitted.
The problem exists only in Linux, on Windows machine data is parsed correctly
Tested with PHP 7.0.22, 7.1.9, 7.1.10.

Workaround:

Fixed it by manually changing fields type to datetime2(0) and enforcing protected $dateFormat = 'Y-m-d H:i:s' for model classes.

But I would like to use the default approach.

[leandroruel]

this is bad, i'm using this driver too

[ownmaster]

I'm thinking, I posted this issue in the wrong place. Seems like this is exactly driver's problem, nothing to do with Laravel.

[ownmaster]

UPDATE: this is Laravel's problem, not the driver's.

Following this issue, I prepared a sample raw PHP script and checked it both on Windows 10 and Ubuntu 16, and on both systems it returned milliseconds.

$db = new PDO("sqlsrv:server=$server;database=$db_name", $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$q = "select ts from dt";
$stmt = $db->query($q);
$value = $stmt->fetchColumn();
var_dump($value);

Script for the table used in the example:

create table dt(
	ts datetime null
)

But, when I read data from the same table via Eloquent, on Windows I get string with milliseconds, on Ubuntu - without milliseconds:
Model:

class Dt extends \Illuminate\Database\Eloquent\Model
{
  protected $table = 'dt';
}

Database configuration:

'sqlsrv' => [
	'driver' => 'sqlsrv',
	'host' => env('DB_HOST', 'localhost'),
	'port' => env('DB_PORT', '1433'),
	'database' => env('DB_DATABASE'),
	'username' => env('DB_USERNAME'),
	'password' => env('DB_PASSWORD'),
	'charset' => 'utf8',
	'prefix' => '',
],

Tested in tinker: App\Dt::first()

[darthtaco]

For those still dealing with this issue (including me), the workaround that @ownmaster provides above works well, provided, however, that you do not need to cast date fields on the pivot table using many-to-many polymorphic relationships. As there is no model for the intermediary (pivot) table, the $getDateFormat property cannot be overridden to supply the expected date format. Therefore, any timestamp fields that are pulled using withPivot() are retrieved using the default date format, and the cast to Carbon objects will fail.

For example:

public function tags()
{
        return $this->morphToMany(Post::class, 'taggable')
                    ->withPivot(['created_at', 'updated_at']);
}

The created_at and updated_at fields on the pivot table are auto-casted to Carbon, and there is no opportunity to disable it because the pivot does not have a model.

[comnuoc]

Hi @ownmaster ,

I have just debugged on Linux and detected that my Laravel app is using dblib (http://php.net/manual/en/ref.pdo-dblib.php) instead of sqlsrv (http://php.net/manual/en/ref.pdo-sqlsrv.php). So I think it causes this issue.

I changed the datetime format:

framework/src/Illuminate/Database/Query/Grammars/SqlServerGrammar.php

Line 457 in 3a31c1a

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

to
return 'Y-m-d H:i:s';
and it worked.

[laurencei]

@darthtaco @ownmaster does this issue happen in the recent version of Laravel 5.7?

I'm wondering if it is an issue with Carbon - there were some changes to how it handles milliseconds.

Are we sure this is a Laravel bug? The fact it works on Windows and not Ubuntu would imply its outside the scope of the framework...

[driesvints]

Closing this issue because it's already solved, old or not relevant anymore. Feel free to reply if you're still experiencing this issue.

[lomotech]

Hi, I'm still having this issue with laravel 5.7 + macos + sqlsrv in docker
but solved when I'm installing pdo_sqlsrv from this https://docs.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-2017#installing-the-drivers-on-macos-el-capitan-sierra-and-high-sierra
and add 2 line to my .zshrc from this microsoft/msphpsql#161 (comment)

[amcsi]

@ownmaster are you sure your server was not using dblib? If you have both sqlsrv and dblib installed, Laravel 5.6 and below prioritize using dblib.

Assuming that the issue is with the dblib driver...

This is still an issue, because the dblib driver gives you dates without milliseconds.

And as per this Laracasts forum post, you can't just override the expected date formats for your model classes, because vendor models with datetimes cannot be overridden, thus e.g. Passport would continue to not work. https://laracasts.com/discuss/channels/laravel/default-date-format

The datetimes can be changed to datetime2; I don't know if that works or not, but I believe it's quite inconvenient to have to manually do things like this in the database.

[ckieffer]

@amcsi I'm using datetime2 and sqlsrv and have this issue. I'm manually managing record created and modified datestamps for now.

Mac OS + Laravel 5.7 + PHP 7.2.x + pdo_sqlsrv

[ownmaster]

@amcsi, I've made some workaround (on application logic level) about this issue and never returned to it after that.

[godfreymakori]

@comnuoc Cheers!