Error 22007 when new access token is created

[bartek-indycki]

Hi

SQLSTATE[22007]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. (SQL: insert into [oauth_access_tokens] ([id], [user_id], [client_id], [scopes], [revoked], [created_at], [updated_at], [expires_at]) values (01cfabaef35ba43b2e84be4dd9b6a4cce14e8950515549c00d42b9e851261a7962f7b0320cf70ac8, 1876, 1, ["*"], 0, 2018-04-03 17:08:51.1000, 2018-04-03 17:08:51.1000, 2019-04-08 17:08:51.904))

I use Laravel 5.5 and passport 4, windows hosting, MS SQL Database.
My code went to production, after internal testing. In testing phase, this error was not thrown. 99% users on production do not see this error.

I Assume that MS SQL default date format has 3 digits representing miliseconds. Passport wants to save:

2018-04-03 17:08:51.1000

but i dont know where that extra digit came from.

It pops out only when date miliseconds are rounded, for example from 2018-04-03 17:08:51.997 to 2018-04-03 17:08:51.1000

I set :

Carbon::setToStringFormat('Y-M-d H:i:s')

in AppServiceProvider, but it didnt solve my problem.

I wanted to dig into framework code and find exact place where dates are formatted before saving to the databse, but without luck.

Than i found the Token Class, and i made an ugly hack:

protected $dateFormat = 'Y-M-d H:i:s';
protected $dates = [
'created_at',
'updated_at',
'expires_at',
];

I dont want to edit passport classes, because composer update will remove my changes.

What is the best solution in this case?

[bartek-indycki]

The problem was date formatting error in PHP < 7.2
https://bugs.php.net/bug.php?id=74753
laravel/framework#22407

SQL Server needs all dates to be formatted as 'Y-m-d H:i:s.v' so:
SqlServerGrammar:

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

so i changed Iluminate/Database/Connection

public function prepareBindings(array $bindings)
    {
        $grammar = $this->getQueryGrammar();

        foreach ($bindings as $key => $value) {
            // We need to transform all instances of DateTimeInterface into the actual
            // date string. Each query grammar maintains its own date string format
            // so we'll just ask the grammar for the format to get from the date.
            if ($value instanceof DateTimeInterface) {
                //in php version < 7.2 DateTime formatting results in errors
                //we need to cut off last digit
                $format = $grammar->getDateFormat();
                $tmpValue = $value->format($format);
                if($format == 'Y-m-d H:i:s.v' && strlen($tmpValue) > 23) {
                    $tmpValue = substr($tmpValue, 0, 23);
                }
                $bindings[$key] = $tmpValue;
            } elseif (is_bool($value)) {
                $bindings[$key] = (int) $value;
            }
        }

        return $bindings;
    }

and Doctrine\DBAL\Types\DateType

 public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if (null === $value) {
            return $value;
        }

        if ($value instanceof \DateTimeInterface) {
            //in php version < 7.2 DateTime formatting results in errors
            //we need to cut off last digit
            $format = $platform->getDateFormatString();
            $tmpValue = $value->format($format);
            if($format == 'Y-m-d H:i:s.v' && strlen($tmpValue) > 23) {
                $tmpValue = substr($tmpValue, 0, 23);
            }
            return $tmpValue;
        }

        throw ConversionException::conversionFailedInvalidType($value, $this->getName(), ['null', 'DateTime']);
    }

Is there more elegant way for doing this?

[driesvints]

I'm afraid not. I'd personally suggest to upgrade to PHP 7.2, sorry.