DateTimeOffset in MSSQL

I need to use a datetimeoffset field in my application as I have data stored from all over the world and need to keep track of timezones. It appears that Lucee doesn’t support this, or maybe it’s Java itself that doesn’t support it. What is the solution, if any?

When you say it’s not supported… what are you referring to?

The MS JDBC driver supports DTO by implementing their own Java class which extends java.sql.Timestamp - so Lucee should handle it just fine.

Otherwise more information on what isn’t working would be helpful.

I had a working query that pulled a dateTime value from MSSQL. When I switched the datatype to dateTimeOffset I started getting errors saying from lucee saying “can’t convert value 2019-07-08 15:20:59.96 +00:00 to a datetime value” This is the function generating the error: datetimeformat(getLastSeen.dLastSeen,“ISO8601”);

If you’re expecting to convert between timezones, you probably want LSDateTimeFormat…

You’d need to determine what the underlying type is for that column. Did Lucee leave it as a dateTimeOffset class? Did Lucee cast it to String?

i.e. #getLastSeen.dLastSeen[1].getClass().getName()#

If Lucee cast it to a String, you’d need to parse it back to a DateTime object and do something with the timezone (LSParseDateTime perhaps), and/or file a JIRA bug to have Lucee do something else. (Perhaps it should instead create a javax.time.ZonedDateTime object, but I am unsure if all the Lucee date/time functions work with the newer javax.time.* API) If I remember correctly, ACF casts it to String, so that’s consistent behavior.

That said, it doesn’t look like Lucee is throwing an error, you just have to do some data manipulation… yes?