Resolving Ignition DATETIMEOFFSET Type -155 Errors

Wrong fixes and their failure modes

The number that matters is -155. It is the SQL Server JDBC type code associated with DATETIMEOFFSET, and the Ignition gateway rejects that result-set type with java.sql.SQLException: Unknown SQL type: -155. This is a data-type translation failure, not a bad timestamp or a query timeout.

Type-translation mechanism

The affected installation ran Ignition 8.0.5 (b2019101516) on Azul Systems Java 11.0.4. Any query returning one of the database's DATETIMEOFFSET columns produced the gateway exception. A query can execute successfully in SQL Server and still fail when the gateway reads its result metadata and tries to map every SQL value into a Java or Ignition data type.

The failure boundary matters. If selecting ordinary columns works but adding the offset column produces -155, database connectivity, credentials, and basic SQL syntax are already functioning. The result projection is exposing a type that this gateway path cannot translate.

Diagnostic boundary checks

1. Run a minimal query that selects a non-time column from an affected table. This confirms the connection and table access without invoking offset translation.
2. Add the DATETIMEOFFSET column unchanged. Reproduction of Unknown SQL type: -155 isolates the failure to the returned type.
3. Project that same value with CAST(... AS nvarchar(30)). A successful result confirms that SQL Server can read the data and that a compatible projection crosses the gateway boundary.
4. Inspect the actual column type rather than relying on its name. A column named StartTimeUTC may be datetime, datetime2, or DATETIMEOFFSET; each requires different offset handling.
5. Decide whether the consumer needs display text, a typed timestamp, or only time filtering. Text is suitable for display and transport, while filtering and arithmetic should remain typed inside SQL Server.

Also record the JDBC driver identity from the gateway connection configuration. A later gateway or driver combination may add the missing mapping, but compatibility must be verified with the actual connection rather than inferred from the Java version.

Compatible projection procedure

For display-only results, perform timezone calculation in SQL Server and return text to Ignition. When the stored column is a UTC datetime without offset metadata, attach UTC first, convert to the target zone second, and cast last:

SELECT CAST(
    StartTimeUTC AT TIME ZONE 'UTC'
                 AT TIME ZONE 'EASTERN STANDARD TIME'
    AS nvarchar(30)
) AS LocalTimeText
FROM SourceTable;

AT TIME ZONE 'UTC' labels the stored clock value as UTC. The second operation calculates the Eastern offset for that instant, including daylight-saving behavior, and nvarchar(30) prevents type -155 from reaching the gateway.

If the source is already DATETIMEOFFSET, omit the first attachment step and convert the existing instant directly to the target zone before casting. Attaching 'UTC' to an already offset-aware value would reinterpret its clock fields instead of merely preserving its instant.

For a typed UTC result rather than display text, convert the value to UTC with SWITCHOFFSET or AT TIME ZONE, then cast to a supported non-offset type. That final cast intentionally drops offset metadata, so the column alias and application contract must state that the returned clock value is UTC.

Sargable UTC filtering

This is heat in the database engine, not gateway logic. Converting every timestamp row consumes CPU and can defeat an index on the time column. Convert the small set of boundary values instead, then compare those values with the unmodified column.

For a UTC datetime column and local Eastern boundary values supplied as parameters, use this shape:

WHERE StartTimeUTC >= CONVERT(datetime,
          CAST(? AS datetime2)
          AT TIME ZONE 'EASTERN STANDARD TIME'
          AT TIME ZONE 'UTC')
  AND StartTimeUTC < CONVERT(datetime,
          CAST(? AS datetime2)
          AT TIME ZONE 'EASTERN STANDARD TIME'
          AT TIME ZONE 'UTC')

The half-open interval includes the start and excludes the end, preventing adjacent shifts from counting the same boundary record twice. Calculate the start and end independently: an interval that crosses a daylight-saving change may span a different number of elapsed hours than the wall-clock schedule suggests.

GETDATE() supplies a clock value without timezone metadata, while GETUTCDATE() supplies a UTC clock value without an offset-bearing type. Attach the intended zone explicitly before conversion. Fixed subtraction based on the current offset fails when one boundary is on standard time and the other is on daylight time.

If part of a predicate cannot avoid a function on the stored timestamp, first use a subquery to prefilter rows with the sargable UTC range. Apply the more complex condition only to that reduced result set.

Verification criteria

1. Confirm that the gateway receives the projected column without Unknown SQL type: -155.
2. Test an ordinary timestamp and timestamps around the daylight-saving boundary. For the demonstrated Eastern conversion, 2018-03-11 07:00:00 UTC maps to 2018-03-11 03:00:00.000 -04:00.
3. Check that the returned text retains the date, time, fractional seconds, and offset when nvarchar(30) is used.
4. Compare row counts at two adjacent shift boundaries. A half-open range must produce neither a duplicate boundary row nor a gap.
5. Inspect the SQL Server execution plan. The time predicate should reference the bare timestamp column so an applicable index can be considered for a seek.

Keep timezone conversion out of the stored column side of the predicate even when a small test appears fast. Time-series cost grows with candidate rows, and a conversion that scans a short interval can become the dominant load over longer retention periods.

FAQ