Return short date from MSSQL database query to avoid timezone conversion

Ignition
,
1

I am storing a short date value in my MSSQL database but this value is returned as datetime type from my named query. This becomes an issue when dealing w timezones west of me - the EventDate column dispays as the previous day to them. A simple way around this is converting my EventDate column to a string but then I lose table formatting on the Ignition side :frowning:. I can live with that solution I guess unless someone has success returning a short date value?

SQL data:
SQLTimestamp

Ignition named query return:
IgnitionTimestamp

2

Java doesn't have a date type without a time component. JDBC has no choice but to apply the DB connection timezone to produce a java object.

Your only solution in a multiple timezone application is to use UTC datetimes everywhere, or use data types that are inherently timezone-aware. Java defaults to the latter.

3

Doh, not the answer I was hoping for, but makes sense... I suppose I will have to alter my database table and the data within.

4

Ignition will be happiest if you always deliver DateTime2 through JDBC. Consider using views to CAST() where needed if DB redesign is too much trouble.