# Mismatch Datetime When Inserting To MS SQL **URL:** \ **Category:** Ignition\ **Created:** [November 18, 2024, 10:25pm UTC](https://forum.inductiveautomation.com/t/mismatch-datetime-when-inserting-to-ms-sql/98762 "2024-11-18T22:25:52Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![csjohnson](https://avatars.discourse-cdn.com/v4/letter/c/5f8ce5/32.png) [@csjohnson](https://forum.inductiveautomation.com/u/csjohnson)\ **Post date:** [November 18, 2024, 10:25pm UTC](https://forum.inductiveautomation.com/t/mismatch-datetime-when-inserting-to-ms-sql/98762/1 "2024-11-18T22:25:52Z")
Ignition v8.1.39 MS SQL Server Express v16.0.1000.6 I've been doing some testing using SQL Express and I am seeing a small discrepancy and I'm not sure if it is caused by something I am doing. Our project requires me to query Ignition Historian for values and merge them into another database table. In my table. there are three columns I use to determine update/insert in the merge query. Columns `equipmentId [int], eventId [int], eventBegin [datetime2(3)]`. The merge will update two other columns `eventEnd [datetime2(3)], eventDuration [float]` if a match is found in the three columns above. I am using `system.tag.queryHistory` to get the data for the merge. The data is being queried using an RTP whose gateway is in a different timezone. In production, the query will be querying the local Historian, not an RTP. I get the tstamp from historian as `java.util.Date` and convert it to a `java.sql.Timestamp`. I do this for a mass number of values, assemble them, and send to a named query as a query string to do the merge. The mismatch I am seeing is in the milliseconds. Any tstamp that has a leading zero in it's ms, the zero is removed and placed at the end. Any other combination it seems to be fine. For example: ```python Values to Insert [8, 2, 2024-11-18 04:41:02.039] [8, 5, 2024-11-18 08:32:12.095] [8, 2, 2024-11-18 08:39:17.019] Values In Table equipId | eventCode | eventBegin 8 | 2 | 2024-11-18 04:41:02.390 8 | 5 | 2024-11-18 08:32:12.950 8 | 2 | 2024-11-18 08:39:17.190 ``` I thought maybe it was because of the column type. I changed the column from `datetime` to `datetime2(3)` last week because the rounding was causing inserts that should have been updates. I did a query to see if there were any rows that had a leading zero. Out of 513000 rows, less than 1% of the rows do. However, they are all `.000`. In the DB Query Browser, I can write an update query and set it with a leading 0 just fine. ---
**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:** [November 18, 2024, 10:37pm UTC](https://forum.inductiveautomation.com/t/mismatch-datetime-when-inserting-to-ms-sql/98762/2 "2024-11-18T22:37:08Z")
Hard to tell without your actual queries to look at, but this is a concern: > [@csjohnson](#): > > assemble them, and send to a named query **as a query string** to do the merge. {My Bold} There's almost certainly no excuse for query string NQ parameters. ---
**Author:** ![csjohnson](https://avatars.discourse-cdn.com/v4/letter/c/5f8ce5/32.png) [@csjohnson](https://forum.inductiveautomation.com/u/csjohnson)\ **Post date:** [November 18, 2024, 10:48pm UTC](https://forum.inductiveautomation.com/t/mismatch-datetime-when-inserting-to-ms-sql/98762/3 "2024-11-18T22:48:16Z")
There could be up to 1000 values to insert/update is why I used the Query String. I wanted to do it in one call instead of n separate queries. I tried using the prepUpdate query but I could not figure out how to get it to not complain about the datetime formatting. ```python IF OBJECT_ID(N'tempdb..#temp_eventLog', 'U') IS NOT NULL DROP TABLE #temp_eventLog; CREATE TABLE #temp_eventLog ( equipment_id INT, event_id INT, event_Begin datetime2(3), event_End datetime2(3), event_Duration float ); INSERT INTO #temp_eventLog (equipment_id, event_id, event_Begin, event_End, event_Duration) VALUES {insertValues} MERGE INTO eventLog AS target USING #temp_eventLog AS source ON (target.event_Begin = source.event_Begin AND target.equipment_id = source.equipment_id AND target.event_id = source.event_id) WHEN MATCHED THEN UPDATE SET target.event_End = source.event_End, target.event_Duration = source.event_Duration WHEN NOT MATCHED BY TARGET THEN INSERT (equipment_id, event_id, event_Begin, event_End, event_Duration) VALUES (source.equipment_id, source.event_id, source.event_Begin, source.event_End, source.event_Duration); ``` ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 18, 2024, 11:03pm UTC](https://forum.inductiveautomation.com/t/mismatch-datetime-when-inserting-to-ms-sql/98762/4 "2024-11-18T23:03:35Z")
Use `runPrepUpdate`, and assemble a query string with the right number of literal `?` placeholders in the outgoing query for the number of values you expect to send. Note that MSSQL has a hardcoded, arbitrary limit on the number of parameters you can send via JDBC; I want to remember it as somewhere between 1-2 thousand. If you're doing anything that requires formatting a datetime to a string, you've already lost. `runPrepUpdate` sends a JDBC PreparedStatement, which slots in _native Java objects_ as query parameters; thus you can losslessly send a `java.util.Date` in and the JDBC driver will handle marshalling that Java type into the appropriate binary format to send into your DB. ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [November 18, 2024, 11:10pm UTC](https://forum.inductiveautomation.com/t/mismatch-datetime-when-inserting-to-ms-sql/98762/5 "2024-11-18T23:10:16Z")
> [@paul-griffith](#): > > I want to remember it as somewhere between 1-2 thousand. MSSQL limit is 2100 parameters via JDBC. I have the value hardcoded for a lot of my data handling scripts to chunk when over that amount. ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [November 18, 2024, 11:23pm UTC](https://forum.inductiveautomation.com/t/mismatch-datetime-when-inserting-to-ms-sql/98762/6 "2024-11-18T23:23:32Z")
For what its worth you can merge without creating a temp table each time by using ```python MERGE INTO TargetTable AS TARG USING(VALUES(?,?,?,?,?), (?,?,?,?,?)) AS SOURCE(Column1, Column2, etc) ON TARG.Column1 = SOURCE.Column1 ``` ---
**Author:** ![csjohnson](https://avatars.discourse-cdn.com/v4/letter/c/5f8ce5/32.png) [@csjohnson](https://forum.inductiveautomation.com/u/csjohnson)\ **Post date:** [November 19, 2024, 12:18am UTC](https://forum.inductiveautomation.com/t/mismatch-datetime-when-inserting-to-ms-sql/98762/7 "2024-11-19T00:18:52Z")
Thank you! I’ll give `runPrepUpdate` another go and see what happens. Paying close attention to keeping the datetimes as `java.util.Date`. I already check the date time args that are passed into my function to see if they are `str` instances, and if so I use `system.date.parse` to get it into a Java date. I have the insert limit set to 1000. I got an error once saying I couldn’t insert more than 1000 at a time. So I just keep the most recent 1000 and discard the rest. Under normal circumstances, it is only inserting/updating ~10 rows. I used the temp table just to shorten the query so I didn’t have to explicitly define all of the columns. Just merge table into table.