Framework: Bad datetime format string and datetime data type for SQL Server on non US platforms

[bgillet]

Laravel version

Laravel Version: 5.7.21

PHP Driver version or file name

php_pdo_sqlsrv_72_ts_x64.dll 5.3.0.11108
php_sqlsrv_72_ts_x64.dll 5.3.0.11108

SQL Server version

SQL Server 2012 Express x64 11.0.6020.0

Client operating system

Windows 7 Pro x64 FR

PHP version

PHP x64 7.2.12

Microsoft ODBC Driver version

ODBC Driver 13 for SQL Server x64 2015.131.4414.46

Same problem with latest ODBC Driver 17 version

Table schema

CREATE TABLE [dbo].[users]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](255) NOT NULL,
    [email] [nvarchar](255) NOT NULL,
    [email_verified_at] [datetime] NULL,
    [password] [nvarchar](255) NOT NULL,
    [remember_token] [nvarchar](100) NULL,
    [created_at] [datetime] NULL,
    [updated_at] [datetime] NULL,
    PRIMARY KEY CLUSTERED ([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Description and steps to reproduce

Using a fresh Laravel 5.7 project installation with Auth module, registration of new user try executing a parameterized SQL statement which throw an PDOException coming from PDO driver. DateTime parameters are sources of the problem. They are given as object and it seems generated string are not ISO compliant so it fails on french operating system.

Parameterized statement :

insert into [users] ([name], [email], [password], [updated_at], [created_at]) values (?, ?, ?, ?, ?)

Executed statement :

insert into [users] ([name], [email], [password], [updated_at], [created_at]) values ('Benjamin GILLET', '[email protected]', 'hashhashhashhashhashhash', '2019-01-17 11:54:10.233', '2019-01-17 11:54:10.233')

Thrown exception :

SQLSTATE[22007]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites.

## Expected behavior

DateTime format in SQL Server is a bit tricky. Default type for DateTime fields is datetime in SQL Server but you should now that this data type is neither ANSI nor ISO8601 compliant.

In Query\SqlServerGrammar.php class in getDateFormat() method, datetime string format used by default is Y-m-d H:i:s.v but this format is not one of the format recognized by datetime SQL Server field type (see https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017 for more details). This should be Ymd H:i:s.v or Y-m-d\TH:i:s.v.

Moreover, formatted datetime is formatted according to the timezone configured in config/app.php file. So, as timezone is not specified in current datetime format string, value recorded in database may not the good one (according to the operating system timezone which may be different than the one configured in config/app.php).

Anyway, for SQL Server datetime datatype, is none of the expected one.

I suggest many fixes :

• Using datetime2 data type instead of datetime with 'c' PHP ISO8061 datetime format string.
• Adding parameters to specify SQL engine's datetime data type and datetime format string.
• Keeping current implementation but changing current datetime format string to 'Ymd H:i:s.v' or 'Y-m-d\TH:i:s.v'.

[driesvints]

I have 0 experience working with Windows Server besides trying to configure some CI builds and this might be besides the point but isn't SQL Server 2012 really old? Aren't there newer versions out there?

[bgillet]

I am using 2012 version because this is the one we are using in my company
and, no, it is not a too old version especially in industrial domain 😋
But of course, I also tested with 2017 version and I have the same problem.

Le jeu. 17 janv. 2019 à 22:03, Dries Vints [e-mail] a
écrit :

I have 0 experience working with Windows Server besides trying to
configure some CI builds and this might be besides the point but isn't SQL
Server 2012 really old? Aren't there newer versions out there?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/laravel/framework/issues/27200#issuecomment-455330877,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AC4UThSltZqclLfry8uw-L7liT2Ms4r5ks5vEOUHgaJpZM4aGl8g
.

[driesvints]

Is this still a problem for you on 5.8?

[bgillet]

No, problem is not solved. Fix #27911 has not been included in 5.8.
Be carefull. If you test today (2019-04-01) on a french system, your query will be OK because SQL Server will consider the date as 2019, January 4th instead of 2019, April 1st. If you test at the end of the month, you will get the error.

[rs-sliske]

if ive understood correctly, the fix here is to explicitly set the dateFormat property on your model class to match the format in your database

[bgillet]

I'm not sure to understand your question.
The issue is data type used for dates in SQL Server is neither ANSI nor ISO8601 compliant as well as the way you write date format string. So, on non US systems, if you use Ymd H:i:s.v date format with datetime data type, the date string you give to the server will be translated against your system local. So, if you have a date after day 12 (ex: 2019-04-29), on NON US CULTURE based system (ex: FRENCH), your query will fail because SQL Server will translate the date as (Y=2019, m=29, d=4) instead of (Y=2019, m=4, d=29). To force SQL Server (and I think other SGBD) translating date string correctly, you should use the ISO8601 date format string Y-m-d\TH:i:s.v.
This is the first fix you should apply to all your date string because this bypass your system culture and today I have not seen any fix commited for this.
Problem for SQL Server is that the datetime datetype is neither ANSI nor ISO8601 compliant so SQL Server will fail translating the date string even if written this way. To make it working, you must use at least datetime2 data type for your date columns. This type is ANSI and ISO8601 compliant. This way, you are sure having a culture independent system.
There is another datatype even better than datetime2 which is datetimeoffset. But this one is to use when you want to support timezone as well. But this is not the purpose of this issue.

[j4kim]

I think I have the same issue...

My environment

• Windows Server 2008 R2 Standard
• PHP version 7.2.7 through IIS version 6.1
• Laravel Framework 5.8.18
• SQL Server 2012 (version 11.0.5058.0)

My code

I have a simple table created using this migration code:

        Schema::create('iban.products', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('ref');
            $table->string('etude');
            $table->timestamps();
        });

And a corresponding Model like:

class Product extends Model
{
    protected $fillable = ['ref', 'etude'];   
}

I try to insert a row using a simple html form calling this controller method:

    public function store(Request $request)
    {
        $p = Product::create($request->all());
        return redirect()->route('product.show', $p->id);
    }

The problem

As @bgillet said, Laravel inverse month and day when inserting column created_atand updated_at. So I have 2019-03-06 14:45:04.333 instead of 2019-06-03 14:45:04.333 in my table. It doesn't trigger any error as we are on the beginning of the month, but last week it did.

What I've tried

Changing these config values:

    'locale' => 'fr_CH',
    'faker_locale' => 'fr_CH',
````
This has no effect at all...
### Force format inverting month and day 
```php
protected $dateFormat = 'Y-d-m H:i:s';

In my model. That works, the values are well formatted in table, but it causes another exception when accessing the values in views:

Trailing data
in Carbon\Carbon rawCreateFromFormat

"ezrabutler" had the same error here but he gave up...

[bgillet]

@j4kim : Yes, you have the same problem. You don't always get an error because when month and day are inverted, it may still be a valid date (ex : 2019-03-06 and 2019-06-03 are still valid but 2019-12-31 and 2019-31-12 are not and crash).
The fix is quite simple : changing datetime data type to datetime2 and using 'Y-m-d\TH:i:s' as date format to make it ISO8601 compliant. But I have not seen any fix yet implemented.
You may also choose working with datetimeoffset data type instead of standard datetime data type. I believe the grammar is well defined to handle this data type.

[j4kim]

@bgillet thank you !
I've tried your solution and it stores dates correctly.
Sadly I still have a Carbon error when accessing the date fields:

(same for datetime2 and datetimeoffset)

[rs-sliske]

trailing data means the date in the database is more precise than the format you have set in the model

[j4kim]

@rs-sliske Thank's a lot

Everything seems to be working fine since I changed my migration adding a _precision_ to the migration Blueprint timestamps method:

Schema::create('iban.products', function (Blueprint $table) {
    // ...
    $table->timestamps(2);
});

Thank's to this stackoverflow response

However I still think this is a bug as it is not documented in official documentation.

[ludo237]

The real bug is the US way of storing date

[driesvints]

So if I understand correctly, this is fixed if you using a precision length on the timestamps method as described here?

[j4kim]

@driesvints yes

[driesvints]

I've sent in a PR to the docs that got merged: https://github.com/laravel/docs/pull/5641