Why Does fromMillis Reject a SQL Server datetime Value?

After the binding preserves the SQL Server datetime as a date value and applies formatting only for display, the conversion error disappears. The failing expression, fromMillis({value}), crosses two incompatible contracts: the source represents a date, while fromMillis requires an integer count of milliseconds.

Accepted Input Contract

The term epoch milliseconds means an integer count of milliseconds from the runtime's epoch reference. fromMillis constructs a date from that count; it does not parse a SQL datetime, format an existing date, or accept every object whose diagnostic name contains “Long.”

1. Check 1: identify what the value means, not just what type(value) prints. Expect a SQL date value when the selected column is datetime; expect an integer only when the query explicitly returns epoch milliseconds.
2. Check 2: compare that meaning with the function contract. Expect fromMillis to remain only on an epoch-millisecond path.

Binding Source and Leaf Selection

The failing path uses a custom property populated by a JSON named-query binding, followed by an expression transform. That introduces multiple type boundaries: database driver, query result, JSON representation, custom property, expression engine, and date function. A value can retain the correct digits while losing the runtime type required by the next layer.

Inspect the exact leaf passed as {value}. Testing the custom property's document, row, or wrapper does not prove the leaf's type. Also inspect the raw named-query result before the expression transform. This separates a query mapping problem from a transform problem.

1. Check 1: read the named-query output before JSON extraction. Expect the selected datetime field to be recognizable as a date value rather than an arbitrary integer.
2. Check 2: inspect the selected JSON leaf and its displayed value. Expect it to match the database row and not a containing object, array, or formatted string.
3. Check 3: temporarily remove fromMillis and return {value}. Expect the custom property to receive the unconverted source value without the long-type error.

Runtime Numeric Type Boundary

type(value) reporting PyLong does not prove that a Java-facing function receives a primitive or boxed Java long. At this boundary, PyLong can be represented by Java BigInteger. A strict function signature can reject that object even though both types represent whole numbers.

Older JDBC drivers can contribute to incorrect numeric mappings, so updating the SQL Server JDBC driver is a valid diagnostic when the query is supposed to return a number. Here, updating the driver did not clear the error. That result moves the decision back to the value's meaning and the expression's target type: the database column is datetime, and the transform is treating it as epoch milliseconds.

The attempted expression fromMillis(toDouble(value)) also retains the mismatch. toDouble deliberately produces a floating-point value, while the reported function contract requires a long.

1. Check 1: rerun the binding without toDouble. Expect the same original date value to remain available for a date-preserving path.
2. Check 2: if the query is intended to return milliseconds, inspect the runtime class presented at the function call. Expect the function's accepted long representation, not BigInteger or double.

Date-Preserving Binding Path

Use this path when the named query selects the SQL Server datetime column itself. The value is already temporal, so constructing another date from milliseconds is the wrong operation. Keep the date value through the binding and format it only at the presentation boundary.

1. Remove the expression fromMillis({value}).
2. Bind the custom property directly to the selected date leaf from the JSON named-query result.
3. If the target must display text, add a Format transform and select the required date presentation in that transform.
4. If downstream logic performs date comparisons, sorting, or date arithmetic, keep the property date-typed and format only the visible component. Converting the shared value to text too early discards date semantics.

A Format transform changes presentation; it does not invent an epoch value and does not repair an incorrectly selected JSON path. The raw value must reach the transform as a date.

1. Check 1: read the custom property with the expression removed. Expect a date value matching the selected database field.
2. Check 2: apply the Format transform. Expect readable date text with no fromMillis type error.
3. Check 3: change the database row selected by the query. Expect the displayed date to change accordingly, proving that the format is applied to live binding data.

Explicit Epoch-Conversion Path

Use fromMillis only when the integration contract explicitly calls for epoch milliseconds. Establish that contract at one boundary. Returning a SQL date sometimes and a numeric timestamp at other times creates a binding that depends on incidental driver behavior.

1. Determine whether the query result is a SQL datetime or an epoch-millisecond integer. Read the value and runtime class at the named-query output.
2. If it is a SQL date, use the date-preserving path.
3. If it is epoch milliseconds, validate that the value is integral and within the destination long type's range.
4. Convert it to the exact long representation accepted by the expression runtime, then pass that result to fromMillis.
5. Do not route the value through toDouble; floating-point conversion changes the type in the wrong direction and can also lose integer precision for sufficiently large values.

Perform the conversion once. Repeated coercion through JSON, strings, doubles, and dates makes failures dependent on locale, precision, and adapter behavior.

1. Check 1: inspect the normalized input immediately before fromMillis. Expect an integral long accepted by the function.
2. Check 2: inspect the output. Expect a date corresponding to the known database record, not merely a call that executes without error.

End-to-End Verification

Test the complete binding after selecting one path. A successful transform alone does not prove that the correct row, JSON field, and date reached the target.

1. Check 1: execute the named query with a record whose datetime is known. Expect the raw query result to match that record.
2. Check 2: inspect the JSON leaf used by the custom property. Expect the same date and no wrapper object.
3. Check 3: inspect the value before the final transform. Expect a date on the Format-transform path or an integral long on the fromMillis path.
4. Check 4: read the bound target. Expect the intended date with no long-type error.
5. Check 5: refresh the binding with a second known record. Expect both the raw value and displayed result to change to that record, proving that no stale or hard-coded conversion remains.

FAQ