Timestamp in datalogger SQL - Kepware

[FG_11085366]

Hi everyone

Basically I'm trying to log data into an SQL server, I get the correct tag value and tag name, but with the time I'm not able to see it correctly in SQL 
In SQL I have the column Time as data type: timestamp

and the data that logs is in hexadecimal, so is not what I want

I've actually use data type datetime2 as well in sql but then I get an error in Kepware (Error Data Logger Plug-in Unable to query recordset on Log Group 'loggroup'. Reason: Conversion failed when converting date and/or time from character string)

You can see it in the snapshots 

does anyone know how can I actually submit the time in the SQL server? or like what datatype should be in Kepware and SQL? please help 

[pshashipreetham · Accepted solution]

Hi, 

This is a SQL column type mapping issue, not a bad timestamp from Kepware.

Kepware DataLogger writes the timestamp as a database datetime field, but in your table the Time column is being detected as SQL_UNKNOWN_TYPE, so the driver falls back to writing the raw value, which is why SQL shows the hex format. That is also why datetime2 throws a conversion error. Kepware is not recognizing that column properly through the SQL driver.

The clean fix is:

• Change the SQL column type to DATETIME instead of timestamp or datetime2

• Recreate or refresh the table mapping in the DataLogger so Kepware re-reads the schema

• Make sure the Timestamp Field in Kepware maps to that DATETIME column

• If needed, use an ODBC DSN / SQL Native Client driver that correctly exposes SQL Server datetime types to Kepware

In short, for SQL Server, use DATETIME for the Kepware timestamp column. timestamp in SQL Server is actually a binary rowversion type, not a real date/time field, which is exactly why you are seeing hex values.

Thanks,