SQL Datetime data being converted to Long?

Hi all. I’m having a datetime conversion issue I’m hoping to get some help with.

I have a “due_date” column in a database table (MS SQL) that is defined as a datetime data type. I have a Named Query that returns data from that table. That NQ is used in a Query Binding on a Perspective Table component’s data parameter. I have a dateFormat parameter defined as “MM/DD/YYYY” for the column the “due_date” value is applied to.

Each row of the table has a “Select” button, that when clicked, passes that row’s data to a function that acts on the data. In the function, I’m getting an error when I use the system.date.format() function on the “due_date” value. That error is “TypeError: format(): 1st arg can't be coerced to java.util.Date”. When I log the data type of the value from the function, it says it is a Long data type.

Here is the log entry for the value:
SO Due Date Value is a type "<type 'long'>". The Value is "1759467600000".

In the SQL table the value is “2025-10-03 00:00:00.000”. In the Perspective table the value is formatted to “10/03/2025” when formatted, but if I select the row and copy it, then paste that into a text editor, the due date value comes out as 1759467600000. Same as the log entry above.

I am not converting the datetime to a Long, so I can only assume that something “under the hood” is doing it. when the binding populates the Perspective table (maybe due to the dateFormat parameter).

Can anyone help me understand why and where this is actually occurring and how I can mitigate this error? I assume I can just convert the Long back to a date and then to a string for further utilization in printing labels, etc.

I believe the table uses JSON (which has no concept of the java.util.Date object or dates in any capacity) to move the data around so it converts the datetime to the UTC millisecond since the epoch value.

Use system.date.fromMillis() to convert it back into a java.util.date object.

e.g.

system.date.format(
	system.date.fromMillis(dueDateValue),
	"MM/DD/YYYY"
)
3 Likes

Thanks Ryan.

I had not even considered that the table uses JSON. Makes sense though.

Any Ignition datetime object that is passed into a Perspective component property is transferred as long millis because javascript doesn't have a native datetime object type. Perspective components that accept a datetime (calendars and datetime inputs) automatically handle these, using the session time zone.

The Perspective table cannot automatically figure this out, but you can set the appropriate entry in props.columns[] to render as date to trigger proper handling.

1 Like