RE: DateFormat error with SQL Server DATETIME2 data type
04/12/20 9:35 PM
I have discovered that it isn't the DateFormat function that is in error but the Microsoft SQL driver which returns the raw value of '2020-04-10 00:00:00.000' rather than the value in the database, an offset of 2 days.
I have also discovered a fix. Download the JDBC driver for SQL Server from https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15 and extract the mssql-jdbc-8.2.2.jre8.jar file placing it into the C:\BlueDragon_Server_JX_71\lib folder then restart the BlueDragon service. This assumes that you have upgraded to Java 8 which is possible be replacing the C:\BlueDragon_Server_JX_71\jre\lib and C:\BlueDragon_Server_JX_71\jre\bin folders with the matching folders from Java 8. In you want to uninstall BlueDragon you will need the original Java 6 folders.
Now the DATETIME2 fields return the correct date. While it is early days, there appears to be no adverse or unexpected results of having the mssql-jdbc-8.2.2.jre8.jar file present. When removing the file, rendering of DATETIME2 by BlueDragon reverts to the 2 day offset.