MS SQL: Incorrect Data Types

This requires editing the ivizgroup.properties on your server in the configuration (conf) directory.

Sometimes a non-standard oddball column type will have a numeric identifier (SQL Type) that is non-standard as well. And in some of those cases, you can tell iDashboards how to treat that type in the ivzgroup.properties:

jdbc.datetimeTypes=321 434 233

would tell iDashboards to treat columns with those SQL types as Datetimes, on the assumption that the JDBC drivers were returning java.sql.Date objects.

Issue:

In the case of datetime2 (and others), the SQL type reported by the driver for that is SQL type 12. But 12 is also the standard SQL type identifier of a VARCHAR column which returns as a string inside the iDashboards chart creation window. In the image below, you can see that the datetime2 column is being treated as a string:

iDashboards v8.5:

Here are some other unusual examples of SQL type identifier driver values as reported from our developers tool QueryForm.

It appears that the datatime2 format is an added data type in SQL Server 2008, and as of 2010, the JTDS drivers didn't support it. In testing this, we found that even the most recent version of the JTDS driver at the time, version 1.3.1 (last updated: ‎6/‎8/‎2013), still did not support DateTime2 format.

Resolution:

1. If there is now a newer version released since 1.3.0, test with the most recent release of the JTDS drivers. Maybe support has been added.
2. Try the Microsoft JDBC drivers for SQL Server.
3. Use SQL to cast or convert the datetime2 column to a normal SQL Server Datetime.
4. Use an iDashboards JavaScript expression to build a Datetime value from the string value in that column.

For More Information:

• Microsoft SQL datetime2 (Transact-SQL)
  
• Source Forge DateTime2 format

Disclaimer: iDashboards Technical Support Engineers are not Database Administrators who know in depth knowledge of them and their quirks. We often learn these skills on the job and have limited knowledge. We do our best to help you with your software in determining if: you are not leveraging the software in the best way for your data or you found a bug in the software, because we want to assist you in your success.

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.