#1431 Cannot edit row from MS SQL Server which contains DateTime2

[Jim Thomas]

Been using SquirreL for years against MS SQL Server and Sybase - best SQL editor! But we recently had to upgrade to SQL Server v14.0 and that necessitated changing many of our DateTime columns in the SQL Server tables to DateTime2. Now SQuirreL will not let me edit any row which has a DateTime2 column in it. I am not editing the DateTime2 column - just a simple varchar/string column in that row. The error is 'Conversion failed when converting date and/or time from character string. Update is probably not safe to do.'

[Gerd Wagner]

Sorry I can't reproduce your problem.
I tested on MSSQL version 14.00.3048 and JDBC driver "Microsoft JDBC Driver 7.2 for SQL Server" version 7.2.0.0.

The table I tested with was created as follows:

create table datetime2Test
(
datetime2TestId Integer not null primary key,
datetime2TestStr Varchar(50),
dateTime2TestCol datetime2
)

[Jim Thomas]

Thanks for checking into it!

Are you running Compatibility Level 120 or lower? In that case you will not see the fractional milliseconds. Here is a quote from Microsoft:

An example of a breaking change protected by compatibility level is an implicit conversion from datetime to datetime2 data types. Under Database Compatibility Level 130, these show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. To restore previous conversion behavior, set the Database Compatibility Level to 120 or lower.

I believe we were formerly at compatibility level 90 and are now at 140. When I get to work on Monday I will try your example although it looks accurate and should create the Modify/Edit issue.

Regards,
Jim Thomas

[Jim Thomas]

I created the exact same table you have in your email response. I cannot 'Make Editable' and change the DateTime2TestStr without an error.

Regards,
Jim Thomas

[Gerd Wagner]

I'm sorry, my MSSQL doesn't seem to allow compatibility levels higher than 120.
Here are the statements I used:

SELECT name, compatibility_level FROM sys.databases

ALTER DATABASE <DB-Name> SET COMPATIBILITY_LEVEL = 120

Perhaps there is somebody out there who has some Java experience, is able reproduce the problem and could have a look at it?