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

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', 'benjamin.gillet@domain.com', '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'.