SQL Error (529): Explicit conversion from data type timestamp to nvarchar is not allowed.

python_dev posted 1 year ago in General
I have multiple tables with timestamps, so the end result of this error is I cannot access the data tab of any of them connected to MSSQL database. I have to manually change the query to VARCHAR instead of NVARCHAR in order to see data in the tables and run as a query..
Is there any way to turn this off????
ansgar posted 1 year ago
The MSSQL TIMESTAMP data type is currently sorted into the binary type category. This is wrong I guess, and it's the cause for why HeidiSQL applies a LEFT(CAST()) to it in the data tab.

So, I will need to move that TIMESTAMP data type into the right category. But I'm having problems in understanding Microsoft's intention for timestamp columns. It's not a simple number, and it's not a readable string, end even not some date/time format. If I insert some row in a table with a timestamp, that timestamp value is auto-filled with some binary character. How does HeidiSQL need to display such values? I guess I must do some internal conversion?
jfalch posted 1 year ago
ahem; thus, most probably cast to int.
sspnet posted 1 year ago
I am having the same problems with MsSQL tables with timestamp fields.

Did you get any further finding the solution?
ansgar posted 1 year ago
I'm still not sure if it will run without any CAST and LEFT call, but I'm going to test that. The posts on stackoverflow says it's a "binary representation of a consecutive number". So probably I have to cast to INT or so.

ansgar posted 11 months ago
This gives me some cosecutive numeric value in a TIMESTAMP column:

SELECT CAST("Column 2" AS INT) FROM "anse"."dbo"."foo";

However, attempts to update a TIMESTAMP column seems to be impossible:

update "anse"."dbo"."foo" set "Column 2"=34007 where "Column 2"=34006
>> SQL Error (272): Cannot update a timestamp column.

But I guess it's ok if at least the value can be seen without triggering some error.
ansgar posted 11 months ago
Should work now without errors, in r4673.

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