Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Datetimeoffset to int convertation not allowed mssql #98

Closed
BlackGad opened this issue Jan 22, 2018 · 7 comments

Comments

Projects
None yet
2 participants
@BlackGad
Copy link

commented Jan 22, 2018

Expected behavior

Correctly show table data

Current behavior

When i click data tab popup window appears
image

Failure information (for bugs)

Table columns in VS SQL provider
image
Table columns in HeidiSQL
image
On HeidiSQL data expand
image

Steps to reproduce

  1. Create DB in MSSQL with table which contains datetimeoffset column
  2. View it with HeidiSQL

Context

  • HeidiSQL version:
    image
  • Database system + version: SQL Express 2016
  • Operating system: WIN 10 x64
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Jan 22, 2018

Please provide the SQL query which causes the error, from the bottom log panel.

@BlackGad

This comment has been minimized.

Copy link
Author

commented Jan 22, 2018

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='OrganizationInviteTickets';
SELECT c.name AS "column", prop.value AS "comment" FROM sys.extended_properties AS prop INNER JOIN sys.all_objects o ON prop.major_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.columns AS c ON prop.major_id = c.object_id AND prop.minor_id = c.column_id WHERE   prop.name='MS_Description'  AND s.name='dbo'  AND o.name='OrganizationInviteTickets';
SELECT C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, K.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON   C.CONSTRAINT_NAME = K.CONSTRAINT_NAME   AND K.TABLE_NAME='OrganizationInviteTickets'   AND K.TABLE_SCHEMA='dbo' WHERE C.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') ORDER BY K.ORDINAL_POSITION;
SELECT TOP 1000  "Id",  "CreatorUserID",  "GuestUserID",  "OrganizationID",  "Created",  "Modified",  "Disabled",  CAST("Timestamp" AS INT),  CAST("Expiry" AS INT) FROM "CinegyLicensePortal"."dbo"."OrganizationInviteTickets";
/* SQL Error (529): Explicit conversion from data type datetimeoffset to int is not allowed. */

It seems that error here that HeidiSQL determine wrong column types

NVARCHAR instead datetimeoffset

@BlackGad

This comment has been minimized.

Copy link
Author

commented Jan 22, 2018

ahh, sorry attached wrong image "Table columns in HeidiSQL" updated above

@BlackGad

This comment has been minimized.

Copy link
Author

commented Jan 22, 2018

Seems in your cycle datetimeoffset is unexpected and column type become from prev column

@BlackGad

This comment has been minimized.

Copy link
Author

commented Apr 13, 2018

Any progress?

@ansgarbecker ansgarbecker removed the needs-info label Jan 3, 2019

@ansgarbecker ansgarbecker added this to the v10.1 milestone Jan 3, 2019

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 22, 2019

Some years ago, in c519225 I fixed the wrong casting from TIMESTAMP columns from a binary value. Now on SQL server 2017, I have still no error with that CAST to INT. On SQL Server 2016, as you showed, you get an error, in even older versions there were no error.

I also found this old forum thread: https://www.heidisql.com/forum.php?t=13041

I suppose HeidiSQL just has to support (= detect) this DATETIMEOFFSET data type, and sort it into the temporal category most probably.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 22, 2019

Should be fixed in the next HeidiSQL build.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.