# Different datetime data types when running script in designer and on gateway as "gateway event script" **URL:** \ **Category:** Ignition\ **Tags:** ignition79\ **Created:** [March 18, 2022, 7:16pm UTC](https://forum.inductiveautomation.com/t/different-datetime-data-types-when-running-script-in-designer-and-on-gateway-as-gateway-event-script/57373 "2022-03-18T19:16:55Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![szcz](https://avatars.discourse-cdn.com/v4/letter/s/ec9cab/32.png) [@szcz](https://forum.inductiveautomation.com/u/szcz)\ **Post date:** [March 18, 2022, 7:16pm UTC](https://forum.inductiveautomation.com/t/different-datetime-data-types-when-running-script-in-designer-and-on-gateway-as-gateway-event-script/57373/1 "2022-03-18T19:16:55Z")
Hi all, we have Ignition 7.9.16 running on Windows Server 2019 with OpenJDK. Now, after enjoying several hours of bug hunting, I came to interesting conclusion. Let’s say I want to populate 2 variables: One variable get’s populated by SQL query where the SQL query returns record from MS SQL database having _datetime_ datatype on the MS SQL server. Second variable gets built with system.date.getDate(…) and system.date.setTime(…) in the script. In Script Console in designer, both varialbes have \ data type. Running the same script on gateway, the variables suddenly become \and \ … and these two cannot be compared by .equals() (did not work for me), but can only be compared by isAfter() and isBefore() methods (that worked for me). That has costed me several hours of time. What did I do wrong? Has any of you any sane explanation? Thank you, take care. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 18, 2022, 7:22pm UTC](https://forum.inductiveautomation.com/t/different-datetime-data-types-when-running-script-in-designer-and-on-gateway-as-gateway-event-script/57373/2 "2022-03-18T19:22:35Z")
`java.sql.Timestamp` is the raw type delivered by JDBC drivers. It is a subclass of `java.util.Date` but with more precision (nanoseconds versus milliseconds). Serialization to go through the network (gateway =\> designer, or gateway =\> Vision client) can squash the precision since Ignition tends to only work with milliseconds. Look at your column definition in MS SQL Server. If you specified more than 3 digits of precision you can get into the equality problem. IIRC, the default is 6 digits. ---
**Author:** ![szcz](https://avatars.discourse-cdn.com/v4/letter/s/ec9cab/32.png) [@szcz](https://forum.inductiveautomation.com/u/szcz)\ **Post date:** [March 18, 2022, 7:27pm UTC](https://forum.inductiveautomation.com/t/different-datetime-data-types-when-running-script-in-designer-and-on-gateway-as-gateway-event-script/57373/3 "2022-03-18T19:27:14Z")
Greetings, thanks for almost immediate reply and the explanation. The confusion is smaller now, but still … why would the software use one class in script console and the other one while running as a gateway script? Pardon my ignorance here … SQL server’s [datetime] data type does not have precision, does it? Data type [datetime2] has but that one I am not using. Thank you, take care. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 18, 2022, 7:34pm UTC](https://forum.inductiveautomation.com/t/different-datetime-data-types-when-running-script-in-designer-and-on-gateway-as-gateway-event-script/57373/4 "2022-03-18T19:34:52Z")
> [@szcz](#): > > SQL server’s [datetime] data type does not have precision, does it? You're right, it doesn't. In fact, its precision is some strange fraction of seconds. > [@szcz](#): > > why would the software use one class in script console and the other one while running as a gateway script? The gateway generally takes whatever JDBC gives it to avoid reprocessing. Passing that result over the network requires encoding and decoding, which can be lossy. In general, do not use the script console for anything but the simplest tests for gateway code, or to trigger messages. Create a temporary message handler that will call your gateway code _in the gateway_ when your script console requests it. The script console is closest to a Vision Client's environment, but even that isn't perfect. ---
**Author:** ![peter](https://avatars.discourse-cdn.com/v4/letter/p/278dde/32.png) [@peter](https://forum.inductiveautomation.com/u/peter)\ **Post date:** [March 19, 2022, 2:56pm UTC](https://forum.inductiveautomation.com/t/different-datetime-data-types-when-running-script-in-designer-and-on-gateway-as-gateway-event-script/57373/5 "2022-03-19T14:56:28Z")
> [@pturmel](#): > > > [@szcz](#): > > > > SQL server’s [datetime] data type does not have precision, does it? > > You're right, it doesn't. In fact, its precision is some strange fraction of seconds. FWIW The MS SQL [datetime](https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15) has: ```auto Accuracy: Rounded to increments of .000, .003, or .007 seconds User-defined fractional second precision: No ``` There's also a table in that link that gives examples of rounding in datetime. Note that MS also says: > Use the **time** , **date** , **datetime2** and **datetimeoffset** data types for new work. These types align with the SQL Standard. They are more portable. **time** , **datetime2** and **datetimeoffset** provide more seconds precision. **datetimeoffset** provides time zone support for globally deployed applications. Datetime2 has precision down to 100ns, and you _do_ have user defined precision. ---
**Author:** ![szcz](https://avatars.discourse-cdn.com/v4/letter/s/ec9cab/32.png) [@szcz](https://forum.inductiveautomation.com/u/szcz)\ **Post date:** [March 23, 2022, 6:53am UTC](https://forum.inductiveautomation.com/t/different-datetime-data-types-when-running-script-in-designer-and-on-gateway-as-gateway-event-script/57373/6 "2022-03-23T06:53:00Z")
Hello, thanks for pointing an interesting facts about the date and time data types in SQL server. Much valuable and worth remembering. Thank you. ---
**Author:** ![szcz](https://avatars.discourse-cdn.com/v4/letter/s/ec9cab/32.png) [@szcz](https://forum.inductiveautomation.com/u/szcz)\ **Post date:** [March 23, 2022, 6:56am UTC](https://forum.inductiveautomation.com/t/different-datetime-data-types-when-running-script-in-designer-and-on-gateway-as-gateway-event-script/57373/7 "2022-03-23T06:56:57Z")
Hello, thanks for your explanation. Much clearer now and also helpful. Have a great day.