Hello,
We are facing problmes in conditions bellow
- SQL Server 2016
- Data base with Compatibility level 130 (It's necessary)
- DB Provider to SQL Native Client (It's necessary)
- "Datetime" Fields
- "Datetime" parameters
All "datetime" parameters seems to be mapped to "detetime2(7)"
The problem is that with compatibilty level 130, conversions from "datetime2(7)" to "datetime" dosen't match... for precision reasons...
Shall we move all ower table fields to "datetime2(3)" (which seems to be correct) or is there a way to customize the default mappings ?
Ali
SQL Server 2016 Compatibility Level 130 : datetime vs datetime2
Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2
To solve the problem, you can use fields with the datetime type instead of datetime2 in the tables. More details about date and time types at MSDN: https://docs.microsoft.com/en-us/sql/t- ... ansact-sql
Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2
Hello Azyk,
Thank you for your response.
Did you make some tests using "datetime" field and querying you table against that field using a parameter ?
Currently, DbExpress or any other client layer (Native Client Client ?) is mapping the Delphi Query parameter to Datetime2(7)....
With compatibility Level to 130 this is wrong... "datetime" and "datetime2" are differents...
Let's assume you created your table like this....
CREATE TABLE [dbo].[MY_TEST_TABLE](
[MY_ID] [int] NOT NULL,
[MY_LABEL] [varchar](50) NULL,
[MY_DATETIME] [datetime] NULL,
CONSTRAINT [PK_MY_TEST_TABLE] PRIMARY KEY CLUSTERED
(
[MY_ID] ASC
)
)
The Delphi code bellow will ends with no records on the "Select" query... Unless an explicit CAST is added !!!!
procedure TForm2.btnTestClick(Sender: TObject);
var
ADateTimeValue : TDateTime;
ARecCount : Integer;
begin
MyQuery.SQL.Text := 'DELETE FROM MY_TEST_TABLE';
MyQuery.ExecSQL;
ADateTimeValue := Now();
MyQuery.SQL.Text := 'INSERT INTO MY_TEST_TABLE(MY_ID, MY_LABEL, MY_DATETIME)'
+ ' VALUES(:MY_ID, :MY_LABEL, :MY_DATETIME)';
MyQuery.Params.ParamByName('MY_ID').AsInteger := 1;
MyQuery.Params.ParamByName('MY_LABEL').AsString := 'DATA LABEL';
MyQuery.Params.ParamByName('MY_DATETIME').AsDateTime := ADateTimeValue;
MyQuery.ExecSQL;
MyQuery.SQL.Text := 'SELECT * FROM MY_TEST_TABLE WHERE MY_DATETIME = :MY_DATETIME';
MyQuery.Params.ParamByName('MY_DATETIME').AsDateTime := ADateTimeValue;
MyQuery.Open;
end;
The reason is the parameter mapping to "datetime2(7)"
You can reproduce directly the problem outside Delphi and DbExpress... For example, in Management Studio.
- Create the table
- Insert a record like this
exec sp_executesql N'INSERT INTO MY_TEST_TABLE
(
MY_ID,
MY_LABEL,
MY_DATETIME
)
VALUES
(
@P1,
@P2,
@P3
)',N'@P1 int,@P2 varchar(50),@P3 datetime2(7)',1,'DATA LABEL','2017-07-16 15:22:54.1830000'
- Process the Select query, using datetime2(7) parameter
exec sp_executesql N'SELECT
*
FROM
MY_TEST_TABLE
WHERE
MY_DATETIME = @P1',N'@P1 datetime2(7)','2017-07-16 15:22:54.1830000'
=> No result !!
Thank you for your response.
Did you make some tests using "datetime" field and querying you table against that field using a parameter ?
Currently, DbExpress or any other client layer (Native Client Client ?) is mapping the Delphi Query parameter to Datetime2(7)....
With compatibility Level to 130 this is wrong... "datetime" and "datetime2" are differents...
Let's assume you created your table like this....
CREATE TABLE [dbo].[MY_TEST_TABLE](
[MY_ID] [int] NOT NULL,
[MY_LABEL] [varchar](50) NULL,
[MY_DATETIME] [datetime] NULL,
CONSTRAINT [PK_MY_TEST_TABLE] PRIMARY KEY CLUSTERED
(
[MY_ID] ASC
)
)
The Delphi code bellow will ends with no records on the "Select" query... Unless an explicit CAST is added !!!!
procedure TForm2.btnTestClick(Sender: TObject);
var
ADateTimeValue : TDateTime;
ARecCount : Integer;
begin
MyQuery.SQL.Text := 'DELETE FROM MY_TEST_TABLE';
MyQuery.ExecSQL;
ADateTimeValue := Now();
MyQuery.SQL.Text := 'INSERT INTO MY_TEST_TABLE(MY_ID, MY_LABEL, MY_DATETIME)'
+ ' VALUES(:MY_ID, :MY_LABEL, :MY_DATETIME)';
MyQuery.Params.ParamByName('MY_ID').AsInteger := 1;
MyQuery.Params.ParamByName('MY_LABEL').AsString := 'DATA LABEL';
MyQuery.Params.ParamByName('MY_DATETIME').AsDateTime := ADateTimeValue;
MyQuery.ExecSQL;
MyQuery.SQL.Text := 'SELECT * FROM MY_TEST_TABLE WHERE MY_DATETIME = :MY_DATETIME';
MyQuery.Params.ParamByName('MY_DATETIME').AsDateTime := ADateTimeValue;
MyQuery.Open;
end;
The reason is the parameter mapping to "datetime2(7)"
You can reproduce directly the problem outside Delphi and DbExpress... For example, in Management Studio.
- Create the table
- Insert a record like this
exec sp_executesql N'INSERT INTO MY_TEST_TABLE
(
MY_ID,
MY_LABEL,
MY_DATETIME
)
VALUES
(
@P1,
@P2,
@P3
)',N'@P1 int,@P2 varchar(50),@P3 datetime2(7)',1,'DATA LABEL','2017-07-16 15:22:54.1830000'
- Process the Select query, using datetime2(7) parameter
exec sp_executesql N'SELECT
*
FROM
MY_TEST_TABLE
WHERE
MY_DATETIME = @P1',N'@P1 datetime2(7)','2017-07-16 15:22:54.1830000'
=> No result !!
Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2
Different behavior cause for Compatibility level 130, which you wrote about is a bug in SQL Server 2016, we cannot affect this behavior. You can learn more about this bug on the SQL Server developers site: https://connect.microsoft.com/SQLServer ... ls/3104723
-
- Posts: 1
- Joined: Sat 17 Nov 2018 12:07
Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2
Hi azyk,
I have got this bug in SQL Server 2016 with Compatibility Level 130.
I could not find anything about it on this site: https://connect.microsoft.com/SQLServer ... ls/3104723
This bug happens when datatype of the PRIMARY KEY table is DateTime.
Thank you.
I have got this bug in SQL Server 2016 with Compatibility Level 130.
I could not find anything about it on this site: https://connect.microsoft.com/SQLServer ... ls/3104723
This bug happens when datatype of the PRIMARY KEY table is DateTime.
Thank you.
Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2
Unfortunately, we can't reproduce the issue. To investigate this behavior of UniDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html
You can send the sample using the contact form at our site: devart.com/company/contactform.html