Laravel, SQL Server, Dates and Time

Photo by Jeet Dhanoa on Unsplash

I’ve recently had issues with date formats when using Laravel Valet with Microsoft SQL Server on an M1 MacBook Air.

Using

Carbon::parse($date)->format('d/m/Y')

where $date is taken from the result of an Eloquent query to the SQL Server database from a column with a date data type, results in this error:

DateTime::__construct(): Failed to parse time string (Jun 23 2021 12:00:00:AM) at position 20 (:): Unexpected character

And sure enough our trusty dd() shows that $date looks like:

Jul  23 2021 12:00:00:AM

But why? Isn’t it supposed to be a date data type?

Somehow the default php database driver used by Valet for SQL Server, pdo_dblib, mangled the date into this format so that when Carbon attempted to parse() it it ended up with the error above.

I then found out that pdo_dblib had settings in:

/opt/homebrew/etc/locales.conf

which looked like:

[default]
 date format = %b %e %Y %I:%M:%S:%z%p
[en_US]
 date format = %b %e %Y %I:%M:%S:%z%p
 language = us_english
 charset = iso_1
[es_ES]
 date format = %b %d %Y %I:%M%p
 language = spanish
 charset = iso_1
[pt_BR]
 date format = %d/%m/%Y %H:%M
 language = Portuguese
 charset = iso_1
[it_IT]
 date format = %d/%m/%Y %H:%M
 language = Italiano
 charset = iso_1

By changing

[default]
 date format = %b %e %Y %I:%M:%S:%z%p

to

[default]
 date format = %b %e %Y %H:%M:%S

and restarting php:

sudo brew services restart php@7.4

The date format became:

Jul  23 2021 00:00:00

which Carbon had no trouble parse()-ing.

I’ve had success before using a newer driver called pdo_sqlsrv (no configuration changes needed) instead of pdo_dblib but unfortunately I couldn’t properly add it to php to run for the M1/ARM MacBook Air and ended up with this error:

Symfony\Component\Debug\Exception\FatalErrorException : Unknown processor architecture.

so I had no choice but to use pdo_dblib.

Furthermore, had I’ve been successful in adding pdo_sqlsrv to php such as in cases with Intel-based Macs, if pdo_dblib still existed as a module in php Laravel 5.6 would still prioritize it over pdo_sqlsrv as shown in src/Illuminate/Database/Connectors/SqlServerConnector.php line 41:

protected function getDsn(array $config)
{
    // ...
    if (in_array('dblib', $this->getAvailableDrivers())) {
        return $this->getDblibDsn($config);
    } elseif ($this->prefersOdbc($config)) {
        return $this->getOdbcDsn($config);
    }

    return $this->getSqlSrvDsn($config);
}

so you’d have to either get rid of pdo_dblib which is not so easy in Valet or modify SqlServerConnector.php which is not really advisable because it’s not picked up by version control.