Explicit conversion from data type datetime2 to int is not allowed

panofish's profile image panofish posted 9 years ago in General Permalink
When I try to view the "data" tab for my MSSQL table, I get the error "SQL Error (592): Explicit conversion from data type datetime2 to int is not allowed" and no data is displayed.

My table definition is:

CREATE TABLE IF NOT EXISTS "AvailabilityTypes" (
"Id" BIGINT NOT NULL DEFAULT NULL,
"Code" NVARCHAR(50) NOT NULL DEFAULT NULL,
"Title" NVARCHAR(50) NOT NULL DEFAULT NULL,
"RowVersion" TIMESTAMP NOT NULL DEFAULT NULL,
"CreationDate" DATETIME2 NOT NULL DEFAULT (sysutcdatetime()),
PRIMARY KEY ("Id")
);

When I select the "data tab", HeidiSQL is attempting to execute the following command to display the data:

SELECT TOP 1000 "Id", "Code", "Title", CAST("RowVersion" AS INT), CAST("CreationDate" AS INT) FROM "database"."schema"."AvailabilityTypes";

The previous SELECT works in the "query tab" if I remove the CAST command for CreationDate.

My question: Am I doing something wrong OR is this a minor bug in HeidiSQL that Ansgar can fix?
ansgar's profile image ansgar posted 9 years ago Permalink
HeidiSQL does not yet know nothing about that DATETIME2 column type. Is that something new in the current MSSQL version? Do you have some documentation link for me so I can add more missing data types?
panofish's profile image panofish posted 9 years ago Permalink
Result from running "Select @@version" on the MSSQL server I am referencing:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4321.0 (X64)
Jul 9 2014 15:59:57
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Here is some datatype information for SQL Server 2008 R2: http://technet.microsoft.com/en-us/library/ms187752(v=sql.105).aspx
Code modification/commit from ansgar.becker, 9 years ago, revision 4848
Add support for MSSQL datetime2 column type. See http://www.heidisql.com/forum.php?t=16708
ansgar's profile image ansgar posted 9 years ago Permalink
r4848 supports DATETIME2 columns now.
panofish's profile image panofish posted 9 years ago Permalink
Excellent! I will test on Monday. Thanks Ansgar, your awesome.

Please login to leave a reply, or register at first.