Saving DateTime in MSSQL breaks

[stmeyer]

Hello,

when saving a "DateTime" column in MSSQL it fails: "Error: SQLSTATE[22007]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Bei der Konvertierung eines nvarchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs." I use CakePHP 3, SqlSRV 3.1 and MSSQL 2014.

Best regards

[dakota]

I have had this problem before. Check the default value in SQLServer, if
it's not valid date time (for instance it's an empty string) you'll get
this error regardless of what the value you are trying to save is.

On Mon, 23 Feb 2015, 17:36 stmeyer notifications@github.com wrote:

Hello,

when saving a "DateTime" column in MSSQL it fails: "Error:
SQLSTATE[22007]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Bei
der Konvertierung eines nvarchar-Datentyps in einen datetime-Datentyp liegt
der Wert außerhalb des gültigen Bereichs." I use CakePHP 3, SqlSRV 3.1 and
MSSQL 2014.

Best regards

—
Reply to this email directly or view it on GitHub
#5951.

[markstory]

We have a bunch of tests for SQLServer that are currently passing, so saving does work in some scenarios.

[stmeyer]

The database table is:

CREATE TABLE [dbo].[User](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](100) NULL,
    [SurName] [nvarchar](100) NULL,
    [LoginName] [varchar](100) NULL,
    [Password] [varchar](64) NULL,
    [Email] [varchar](100) NULL,
    [DateCreate] [datetime] NULL,
    [DateLastVisit] [datetime] NULL
 CONSTRAINT [PKUser] 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]

Then i bake everything for the table:

cake.bat bake model user
cake.bat bake controller user
cake.bat bake template user

After this, i visit https://url.tld/user/add and fill out all field. By saving, the executed query object is:

object(Cake\ORM\Query) {

    'sql' => 'INSERT INTO [user] ([FirstName], [SurName], [LoginName], [Password], [Email], [DateCreate], [DateLastVisit]) OUTPUT INSERTED.* VALUES (:c0, :c1, :c2, :c3, :c4, :c5, :c6)',
    'params' => [
        ':c0' => [
            'value' => 'test',
            'type' => 'string',
            'placeholder' => 'c0'
        ],
        ':c1' => [
            'value' => 'test',
            'type' => 'string',
            'placeholder' => 'c1'
        ],
        ':c2' => [
            'value' => 'test',
            'type' => 'string',
            'placeholder' => 'c2'
        ],
        ':c3' => [
            'value' => 'test',
            'type' => 'string',
            'placeholder' => 'c3'
        ],
        ':c4' => [
            'value' => 'test@mail.com',
            'type' => 'string',
            'placeholder' => 'c4'
        ],
        ':c5' => [
            'value' => object(Cake\I18n\Time) {},
            'type' => 'timestamp',
            'placeholder' => 'c5'
        ],
        ':c6' => [
            'value' => object(Cake\I18n\Time) {},
            'type' => 'timestamp',
            'placeholder' => 'c6'
        ]
    ],
    'defaultTypes' => [
        'User.ID' => 'integer',
        'ID' => 'integer',
        'User.FirstName' => 'string',
        'FirstName' => 'string',
        'User.SurName' => 'string',
        'SurName' => 'string',
        'User.LoginName' => 'string',
        'LoginName' => 'string',
        'User.Password' => 'string',
        'Password' => 'string',
        'User.Email' => 'string',
        'Email' => 'string',
        'User.DateCreate' => 'timestamp',
        'DateCreate' => 'timestamp',
        'User.DateLastVisit' => 'timestamp',
        'DateLastVisit' => 'timestamp'
    ],
    'decorators' => (int) 0,
    'executed' => false,
    'hydrate' => true,
    'buffered' => true,
    'formatters' => (int) 0,
    'mapReducers' => (int) 0,
    'contain' => [],
    'matching' => [],
    'extraOptions' => [],
    'repository' => object(App\Model\Table\UserTable) {

        'registryAlias' => 'User',
        'table' => 'user',
        'alias' => 'User',
        'entityClass' => 'App\Model\Entity\User',
        'associations' => [[maximum depth reached]],
        'behaviors' => [[maximum depth reached]],
        'defaultConnection' => 'default',
        'connectionName' => 'default'

    }

}

Is it possible, that DateTime-Columns in MSSQL have to be a PHP-DateTime-Object, like described in https://msdn.microsoft.com/en-us/library/cc296193%28v=sql.105%29.aspx

[dakota]

Cake\I18n\Time are php DateTime objects. What is the default value on the date fields (Use SQL management studio to inspect)?

[stmeyer]

There are no default values (except the ID column). I have recreated the table with the SQL statement above. Additionaly i have checked the default values in the management studio: there is nothing inserted.

[dakota]

I've made extensive use of MSSQL with Cake3 and the only time I've ever seen that error is if the default value is "" (i.e. an empty string), it might be that nothing is treated like an empty string? Try setting default to null.

What is the actual query that is executed? Either use DebugKit, or enable query logging on the table.

[stmeyer]

I have set the default value for the DateTime columns to NULL. The error still occurs. In DebugKit the Sql Log shows

INSERT INTO [user] ([FirstName], [SurName], [LoginName], [Password], [Email], [DateCreate], [DateLastVisit]) OUTPUT INSERTED.* VALUES ('test', 'test', 'test', 'test', 'test@mai.com', '2015-02-24 10:27:00', '2015-02-24 10:27:00')

Executing the Query in the SQL Management Studio shows the same error:

Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

When i excecute the following Query in SQL Management Studio, it works fine:

INSERT INTO [user] ([FirstName], [SurName], [LoginName], [Password], [Email], [DateCreate], [DateLastVisit]) OUTPUT INSERTED.* VALUES ('test', 'test', 'test', 'test', 'test@mai.com', '24.02.2015 10:27:00', '24.02.2015 10:27:00')

Are there maybe some settings for formating the German date time format? When I excecute the Query dbcc useroptions, i get as result: dateformat dmy

[dakota]

Wow. Yet another thing that SQL Server does differently than every other relational database. This means that SQL Server is not accepting ISO 8601 datetime formats when localised. Which I must admit is the strangest thing I've ever seen!

[stmeyer]

Has anyone an idea, what i can do in this case? Is there a possibility to change the date format for Sqlserver in CakePHP 3?

[markstory]

We currently don't have a setter for setting the date/datetime/time format on the database types, but I can add one.

[markstory]

Before I add a format setter, does German SQLServer also return datetimes in the localized format?

[lorenzo]

@markstory would it be worth it to ask the driver for the format to use? By default all drivers would return 'Y-m-d H:i:s' but SQLServer could execute a query to get the locale format.

[stmeyer]

@markstory This is an confusing point. I have no problem to display DateTime-Columns with CakePHP 3. So, I don't think the German SQLServer returns datetimes in the localized format.

[stmeyer]

I have found a workaround: In the database configuration i set

'init' => ['SET LANGUAGE us_english']

With this, I am able to INSERT and UPDATE DateTime-Columns. But I do not think, that this is a nice solution.

[markstory]

If SQLServer is taking datetimes in the same localized format that it outputs them, then we could potentially allow the default formats to be set in the type converter. If the formats are different we can't really do that though. Knowing that information would be helpful.

[stmeyer]

I'm very new to CakePHP, so please give me a hint, how to see the output dateformat of SQLServer. Until today I have only worked with the normal sqlsrv-Driver (without PDO). This driver is returning PHP-DateTime-Objects. Would it be helpful, if i write a litte stand allone programm unsing sqlsrv PDO to see the results of datetime-columns?

[ravage84]

@stmeyer are you German speaking? Because I ran into the same problem and solved it by using the following connection array in CakePHP 2.x

$default = array(
    'datasource' => 'Database/Sqlserver',
    'persistent' => false,
    'host' => 'localhost',
    'login' => '',
    'password' => '',
    'database' => 'cakephp_app',
    'prefix' => '',
    'encoding' => PDO::SQLSRV_ENCODING_UTF8,
    'settings' => array(
        'DATEFORMAT' => 'ymd', // Make sure the date format is the right one
        'LANGUAGE' => 'us_english' // Not sure anymore if this is necessary for that problem, too
    ),
);

Havent worked with CakePHP 3.0 and SQLServer yet, though.

[CauanCabral]

I don't use SQLServer, but your problem seems like which I'd using Portuguese Date in PostgresSQL.

For PG, the solution was define date format in app.php

['Datasources']['default']['init'] = ['SET datestyle TO ISO, dmy']

You can also configure parsing Date in models: http://book.cakephp.org/3.0/en/orm/database-basics.html#parsing-localized-datetime-data

[markstory]

@stmeyer If PDO sqlsrv is returnning datetime objects then we won't need to parse them which is good.

[stmeyer]

@markstory I have made a little script:

<pre>
<?php
$pdo = new PDO("...","...","...");
$sql = 'SELECT * from [user]';
foreach ($pdo->query($sql) as $row) {
    var_dump($row);
}
?>
</pre>

As a result i get a the datetime as a string yyyy-mm-dd hh:mm:ss:000

array(16) {
  ...
  ["DateCreate"]=>
  string(23) "2015-02-26 08:50:00.000"
  [6]=>
  string(23) "2015-02-26 08:50:00.000"
  ["DateLastVisit"]=>
  string(23) "2015-02-26 08:50:00.000"
  [7]=>
  string(23) "2015-02-26 08:50:00.000"
}

But as i have written, reading DateTime-Columns with CakePHP 3 is not the problem.

[markstory]

Ok, that's annoying as I would have hoped that there would be symmetry between the insertion and output formats.

[markstory]

Since we won't have a reasonable way to work around this SQLServer quirk, I think the workaround suggested by @CauanCabral and @stmeyer where the connection is set to en-us to get ISO formatted dates is probably the simplest option right now.