SQL Server 2016 Compatibility Level 130 : datetime vs datetime2

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Post Reply
salvador
Posts: 6
Joined: Fri 05 Nov 2004 11:57

SQL Server 2016 Compatibility Level 130 : datetime vs datetime2

Post by salvador » Tue 04 Jul 2017 15:23

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

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2

Post by azyk » Fri 14 Jul 2017 08:22

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

salvador
Posts: 6
Joined: Fri 05 Nov 2004 11:57

Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2

Post by salvador » Sun 23 Jul 2017 11:24

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 !!

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2

Post by azyk » Thu 27 Jul 2017 06:31

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

hojjat.delfan
Posts: 1
Joined: Sat 17 Nov 2018 12:07

Re: SQL Server 2016 Compatibility Level 130 : datetime vs datetime2

Post by hojjat.delfan » Sat 17 Nov 2018 12:17

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.

Post Reply