VERIFIED SOLUTION i
X

Resolve the issue of database connections converting Date columns to String fields in Spectrum

Issue

When using Spectrum to connect to a Microsoft SQL Server instance with the included SQLServer JDBC driver, the software will incorrectly convert any column in the SQL table that is Date type to a string type.  For example, if this SQL is used to create the table:
 
CREATE TABLE [dbo].[DateFormatTest](
	[ID] [int] NULL,
	[StartDate] [date] NULL,
	[EndDate] [date] NULL,
	[ModDate] [datetime] NULL
)

then Spectrum stages that access that data will incorrectly show the StartDate and EndDate columns as strings.  Example of these stages include:
  • Read from DB
  • Write to DB
  • Query DB

Date converted to string

 

Cause

Spectrum uses a third-party JDBC driver from the jTDS project to provide out-of-the-box support for MS SQL Server connections.  The root cause of this incorrect conversion is a bug in that driver code.  

Resolution

UPDATED: August 3, 2017


There are two ways to work around this issue in Spectrum.
  1. If possible, convert the Date fields in the MS SQL Server table to use the DateTime format.  This data type is correctly supported in the driver.
  2. Download and install a different JDBC driver to use when connecting to a MS SQL Server database.  The JDBC driver from Microsoft correctly handles columns with the Date data type.
The Spectrum documentation has steps for configuring new JDBC drivers through Management Console.  Refer to the documentation for the JDBC driver for information regarding the driver class name and sample connection URL.

Environment Details

Products Affected:  Spectrum Technology Platform, Enterprise Data Integration Module
Product Feature: JDBC Drivers
Operating System: All
Database: MS SQL Server
 

Downloads

  • No Downloads