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 file, for example:
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.
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:
8.0 and earlier:
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 a recently added 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.0 still did not support DateTime2 format.
- 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. http://sourceforge.net/projects/jtds/files/jtds/
- Try the Microsoft JDBC drivers for SQL Server. Those can be a pain, however.
- Use SQL to cast or convert the datetime2 column to a normal SQL Server Datetime.
- Use an iDashboards Analytics expression to build a Datetime value from the string value in that column.
- Private Cloud
- X Platform
- Enterprise Suite