Migration Solutions for ColdFusion Applications to ASP.NET
New Atlanta Product Forums Profile | Search | Login | RSS
New Topic Reply   Previous Page  Page: 1   Next Page

Thread: DateFormat error with SQL Server DATETIME2 data type
Created on: 04/12/20 02:23 AM Replies: 1

Joined: 03/31/08
Posts: 24
DateFormat error with SQL Server DATETIME2 data type
04/12/20 2:23 AM

An observation is that the BlueDragon DateFormat function produces an incorrect date when the source is SQL Server DATETIME2 data type.

For example, if the value in the database for D1 (DATETIME) is '2020-04-12 00:00:00.000' and the value for D2 (DATETIME2(7)) is '2020-04-12 00:00:00.0000000' then

DateFormat(D1, 'd-mmm-yyyy') = 12-Apr-2020
DateFormat(D2, 'd-mmm-yyyy') = 10-Apr-2020

While you could 'CAST(D2 AS DATETIME)' as a work around, that only works if the year is in the range of 1753 to 9999.
Link | Top | Bottom

Joined: 03/31/08
Posts: 24
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.
Link | Top | Bottom

New Post
Please login to post a response.

company media information terms of use privacy policy contact us
This page was dynamically built on the BlueDragon CFML Engine