Weird date formats with sqlsrv

I have the following columns in a MS SQL Server table:

date - date datatype with the value 2019-12-16
start - time(7) datatype with the value 07:00:00.000
end - time(7) datatype with the value 08:00:00.000

When I fetch the model from the database I get the following values:

date - Dec 16 2019 12:00:00:AM       
start - Jan  1 1900 07:00:00:0000000AM         
end - Jan  1 1900 08:00:00:0000000AM

I just want them returned as strings with their normal value.

Looking at this I think the issue is related to the PDO_SQLSRV driver, and I have to set the option "ReturnDatesAsStrings".

I've tried to do this in database config file:

        'app' => [
            'driver' => 'sqlsrv',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'options' => [
                'returnDatesAsStrings' => true,
                PDO::ATTR_STRINGIFY_FETCHES => true
            ],
        ],

But it didn't work. How can I set the returnDatesAsStrings option?

There's a github proposal related here.

rodrigo.pedra

5 years ago

Level 56

Please inform:

PHP version
SQL Server version
PHP SQL driver version

I tested with:

PHP version

PHP 7.4.12 (cli) (built: Oct 31 2020 17:04:25) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.12, Copyright (c), by Zend Technologies

SQL Server version

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) 
	Nov 30 2018 12:57:58 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)

SQL Server running on docker

PHP SQL Server driver version

$ pecl list
Installed packages, channel pecl.php.net:
=========================================
Package    Version State
pdo_sqlsrv 5.8.1   stable
sqlsrv     5.8.1   stable

drivers from: https://github.com/microsoft/msphpsql

`php.ini` config

I use the defaults, but maybe you have something weird in yours

$ php -i | grep sqlsrv
/etc/php/7.4/cli/conf.d/20-sqlsrv.ini,
/etc/php/7.4/cli/conf.d/30-pdo_sqlsrv.ini
Registered PHP Streams => https, ftps, compress.zlib, php, file, glob, data, http, ftp, sqlsrv, zip, phar
PDO drivers => mysql, pgsql, sqlite, sqlsrv
pdo_sqlsrv
pdo_sqlsrv support => enabled
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240
pdo_sqlsrv.log_severity => 0 => 0
pdo_sqlsrv.set_locale_info => 2 => 2
sqlsrv
sqlsrv support => enabled
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.SetLocaleInfo => 2 => 2
sqlsrv.WarningsReturnAsErrors => On => On

Table

create table [dbo].[tests]
(
	[date] date,
	[start] time(7),
	[end] time(7)
)
go

Data

+----------+--------+--------+
|date      |start   |end     |
+----------+--------+--------+
|2019-12-16|07:00:00|08:00:00|
+----------+--------+--------+

Exported from PHPStorm

Connection configuration

// ./config/database.php

        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
            'host' => '127.0.0.1',
            'port' => '1433',
            'database' => 'rodrigo',
            'username' => 'sa',
            'password' => 'redacted',
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
        ],

Test code

<?php

// ./routes/web.php

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Route;

class Test extends Model {
    protected $connection = 'sqlsrv';
    protected $table = 'tests';
}

Route::get('/', function () {
    return Test::all();
});

Results

[
  {
    "date": "2019-12-16",
    "start": "07:00:00.0000000",
    "end": "08:00:00.0000000"
  }
]

Hope you can get some insights from any of these info.