# Converting a varchar data type to a datetime data type created an out of range value **URL:** \ **Category:** Ignition\ **Tags:** ignition80\ **Created:** [April 23, 2020, 12:43pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153 "2020-04-23T12:43:12Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [April 23, 2020, 12:43pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/1 "2020-04-23T12:43:12Z")
I am positive that this issue is related to SQL but still wanted to see if someone experienced something similar. I am writing very simple queries dynamically from a template. For example : ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/43cb04d50a855a289ac95c20602ea21ba7b54658.png) But when it executes it throws following error: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2c035748f3ba6dbcbf164c8eafb9319b1f28dbb9.png) Sorry its in French which translates to : `UPDATE lab_test_compound_specification SET date_edited = '2020-04-22 19: 31: 48.930', maximum = 1.55 WHERE id = 4 SQL error for "UPDATE lab_test_compound_specification SET date_edited = '2020-04-22 19: 31: 48.930', maximum = 1.55 WHERE id = 4 ": Converting a varchar data type to a datetime data type created an out of range value.` One thing I want to mention : The server is located in France and I am located in North America(Canada in particular) Any comments will be appreciated. Thanks ---
**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:** [April 23, 2020, 1:41pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/2 "2020-04-23T13:41:26Z")
This kind of error is common when relying on string construction of queries. You _ **really** _ should be using parameterization so the timestamp is transferred from Ignition to your DB without conversion to a string. This is done with a “Prep” query or a named query. The former would look like: ```auto UPDATE lab_test_compound_specification SET date_edited = ?, maximum = ? WHERE id = ? ``` The latter would look like: ```auto UPDATE lab_test_compound_specification SET date_edited = :DateParm, maximum = :MaxParm WHERE id = :IdParm ``` In both cases, the _values_ for the query are passed _natively_ in JDBC so that locale isn’t an issue. This also protects you from SQL injection security issues. ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [April 23, 2020, 1:53pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/3 "2020-04-23T13:53:52Z")
@pturmel, It is actually done using Prep query ! I showed an example of Database browser query to elaborate it simply. Here’s an actual Error : ```auto Traceback (most recent call last): File "", line 5, in File "", line 412, in saveChanges File "", line 30, in saveChangesToDB File "", line 442, in saveChangesToDB File "", line 489, in updateRow java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate( UPDATE lab_test_compound_specification SET date_edited= ?, maximum= ? WHERE id = ?, [2020-04-22 19:31:48.930, 1.75, 4], sql_lilvsql16, , false, false) caused by Exception: Error executing system.db.runPrepUpdate( UPDATE lab_test_compound_specification SET date_edited= ?, maximum= ? WHERE id = ?, [2020-04-22 19:31:48.930, 1.75, 4], sql_lilvsql16, , false, false) caused by GatewayException: SQL error for " UPDATE lab_test_compound_specification SET date_edited= ?, maximum= ? WHERE id = ?": La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites. caused by SQLServerException: La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites. Ignition v8.0.10 (b2020031912) Java: Azul Systems, Inc. 11.0.6 ``` ---
**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:** [April 23, 2020, 2:02pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/4 "2020-04-23T14:02:42Z")
That suggests your timestamp supplied to the query is already a string. You need to supply a java.util.Date or a java.sql.Timestamp. Please show the code that calls the query. ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [April 28, 2020, 2:00pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/5 "2020-04-28T14:00:15Z")
@pturmel, this is how it looks like : ```auto WITH result_set AS ( SELECT DISTINCT c.compound_name as compound, c.id as compound_id, lton.order_number as Order#, RIGHT(lt.batch_number, 3) as batch_number, COALESCE(CAST(mbh.start_time as varchar), '-') as 'date_mixed', COALESCE(lt.mixer_batch_history_id, -1) as mbh_id, ltn.id as test_id, DENSE_RANK() OVER(PARTITION BY lt.batch_number, ltn.id ORDER BY lt.date_created) AS 'test_number', CASE WHEN ltr.value IS NULL THEN '-' WHEN ltr.pass_customer_specs = 0 THEN '*' + cast( ltr.value as nvarchar(10)) ELSE cast( ltr.value as nvarchar(10) ) END as 'value', lt.pass_customer_specs, lt.id as lab_test_id, lt.date_created as date_tested FROM measurement m JOIN lab_test_measurement ltn ON ltn.measurement_id = m.id JOIN lab_test_result ltr ON ltr.lab_test_measurement_id = ltn.id JOIN lab_test lt ON lt.id = ltr.lab_test_id JOIN lab_test_order_number lton ON lton.lab_test_id = lt.id JOIN compound c ON c.id = (SELECT compound_id from lab_test_compound WHERE id = lt.lab_test_compound_id) JOIN compound_process cp ON cp.compound_id = c.id AND cp.mixing_line_id = (SELECT id FROM mixing_line WHERE line_number = :line_number ) LEFT JOIN mixer_batch_history mbh ON mbh.id = lt.mixer_batch_history_id WHERE lt.date_created between '2020-03-01 00:00:00' AND '2020-03-31 23:59:59' ) SELECT DISTINCT compound, compound_id, order#, batch_number, date_mixed, test_number, COALESCE([1], '-') as 'ml', COALESCE([2], '-') as 'mh', COALESCE([3], '-') as 'ts1', COALESCE([4], '-') as 'ts2', COALESCE([5], '-') as 't10', COALESCE([6], '-') as 't50', COALESCE([7], '-') as 't90', COALESCE([8], '-') as 'mlmin', COALESCE([10], '-') as 'ml1p4', COALESCE([11], '-') as 't5', COALESCE([12], '-') as 't35 (mv)', COALESCE([13], '-') as 'Hardness', COALESCE([14], '-') as 'SPG', CASE pass_customer_specs WHEN 0 THEN 'Fail' ELSE 'Pass' END AS 'Pass?', date_tested FROM ( SELECT * FROM result_set ) p PIVOT ( MIN (value) FOR test_id in ([1], [2], [3], [4], [5], [6], [7], [8], [10], [11], [12], [13], [14]) ) AS pvt ORDER BY date_tested desc, compound, batch_number, test_number ``` ---
**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:** [April 28, 2020, 2:10pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/6 "2020-04-28T14:10:18Z")
That’s not what I asked for. Please show the jython that calls the query, along with the code leading to it that obtains and packages the parameters. ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [April 28, 2020, 2:54pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/7 "2020-04-28T14:54:19Z")
I see. It is component property change script looks like - ```auto if (event.propertyName == 'dateChanged' and event.newValue == 1) or event.propertyName == 'where_clause': event.source.refresh = 1 if event.propertyName == 'refresh' and event.newValue == 1: event.source.refresh = 0 start_date = event.source.getComponent('DateTimeMonthSelection').startDate end_date = event.source.getComponent('DateTimeMonthSelection').endDate where_clause = event.source.where_clause print where_clause line_number = system.tag.read("LineNumber").value # View by mix date and all test results (pass and fail) if event.source.getComponent('PassFail').getComponent('PassedAndFailed').selected == 1: table = event.source.parent.parent.getComponent('PassedAndFailed') results = system.db.runNamedQuery('LabTesting/LabTestExtensive_1', {'clause':where_clause, 'line_number':line_number}) table.data = results ``` Here, where clause is coming from custom property which has expression binding as below - ```auto // Date filter Root Container.Filter.FilterContainer.DateType.selected}, " WHERE mbh.start_time >= '" + {Root Container.Filter.FilterContainer.DateTimeMonthSelection.startDate} + "' AND mbh.end_time <= '" + {Root Container.Filter.FilterContainer.DateTimeMonthSelection.endDate} + "'", " WHERE lt.date_created between '" + {Root Container.Filter.FilterContainer.DateTimeMonthSelection.startDate} + "' AND '" + {Root Container.Filter.FilterContainer.DateTimeMonthSelection.endDate} + "'") + // Last test and pass only if( {Root Container.Filter.FilterContainer.PassFail.LastTestPassedOnly.selected}, ' AND lt.pass_customer_specs != 0', '') + if( {Root Container.Filter.FilterContainer.PassFail.BatchSummary.selected}, ' AND ltr.value != 0', '') ``` Since, we are focused on the DateTimeMonthSelection Component, it is a template which toggles date as selected, Example - This is what I would get if I select March - ```auto from java.util import Calendar # Set the start date cal = Calendar.getInstance() cal.set( Calendar.MONTH, Calendar.MARCH ) cal.set( Calendar.DAY_OF_MONTH, 1 ) cal.set( Calendar.HOUR_OF_DAY, 0 ) cal.set( Calendar.MINUTE, 0 ) cal.set( Calendar.SECOND, 0 ) if cal.getTime() > Calendar.getInstance().getTime() : cal.set( Calendar.YEAR, cal.get(Calendar.YEAR) - 1 ) event.source.parent.getComponent('startDate').date = cal.getTime() # Set the end date cal.set( Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH) ) cal.set( Calendar.HOUR_OF_DAY, 23 ) cal.set( Calendar.MINUTE, 59 ) cal.set( Calendar.SECOND, 59 ) event.source.parent.getComponent('endDate').date = cal.getTime() ``` ---
**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:** [April 28, 2020, 3:58pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/8 "2020-04-28T15:58:31Z")
As I suspected, you are converting the dates to strings to supply in your where clause. Don’t do that. Use two different named queries if you need to, but pass the dates themselves in their own parameters (datetime parameters). Don’t convert them to strings. ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [April 28, 2020, 7:27pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/9 "2020-04-28T19:27:07Z")
I see. One thing I don’t get - Why does this issue happen ? We didn’t had this issue in 7.8 or 7.9 Is it because Ignition 8 ? Since, this is being used at 1000 different places, what is the efficient way of doing it ? ---
**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:** [April 28, 2020, 7:35pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/10 "2020-04-28T19:35:05Z")
Date/time strings are inherently locale-dependent and time zone dependent. Java’s internal format is UTC milliseconds, converted to/from local time wherever needed, and converted to/from strings using the locale. This was also true in earlier versions of Ignition, but less noticeable because Vision clients are also java, and the serialization of the java date/time values between client and gateway preserves UTC milliseconds. Perspective has to deal with JSON and javascript, which doesn’t even have a native date/time datatype. If you encode timestamps as strings for in both directions, with different timezones or locales, you have a nightmare. If you send long integer milliseconds (like the t\_stamp column of Ignition’s tag historian), you get time zone independence, but have to post-process into local formats in the browser. Which is solely performed by IA code, or by third-party module code, not by jython. ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [April 29, 2020, 12:27pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/11 "2020-04-29T12:27:52Z")
@pturmel, I think that’s beyond my tiny brain. Do you have a quick example where I can understand datetime transfer between Ignition and SQL ? ---
**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:** [April 29, 2020, 12:33pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/12 "2020-04-29T12:33:51Z")
Just don’t convert timestamps to strings. Use a colon-prefixed parameter for your timestamps in your named queries, putting at least the beginning of your WHERE clause in the named query. Supply the raw datetime datatype to your named query. Leave the `clause` parameter to deliver just the ANDed parts, if any. You’ll need separate named queries for the two different timestamp WHERE clause structures. { Don’t use an expression binding for your WHERE clause–just construct it in jython prior to calling system.db.runNamedQuery(). } ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [April 29, 2020, 7:45pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/13 "2020-04-29T19:45:22Z")
Phil, I am doing the way you told. Which seems to be working fine except I am getting and error with missing value parameter for the clause. For example : Here the clause is nothing. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/9/901a1155e57258f2e7ee3230e2139de20cc6f3de.png) at throws an error as : ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/6c0bbf26c41f455f724e57affe95b8c74bedc433.png) For temporary, I am using ’ ’ and concatenating other where clauses to the space but I thought passing nothing was acceptable as of 7.9.x ---
**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:** [April 29, 2020, 8:38pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/14 "2020-04-29T20:38:08Z")
> [@kaushik.shah](#): > > For temporary, I am using ’ ’ and concatenating other where clauses to the space but I thought passing nothing was acceptable as of 7.9.x No, starting with an empty string and passing that if neither optional clause is selected is the correct answer. ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [April 30, 2020, 12:59pm UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/15 "2020-04-30T12:59:35Z")
Okay, thanks ! ---
**Author:** ![giovannimarco.giorda](https://avatars.discourse-cdn.com/v4/letter/g/e68b1a/32.png) [@giovannimarco.giorda](https://forum.inductiveautomation.com/u/giovannimarco.giorda)\ **Post date:** [May 1, 2020, 10:16am UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/16 "2020-05-01T10:16:32Z")
Hello, I solved the error in object of this thread directly in SQL query, something like this:CONVERT(DATETIME, ‘2019-08-15’, 102), where in ‘2019-08-15’ you replace the property text of popup calendar. ---
**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:** [May 1, 2020, 11:35am UTC](https://forum.inductiveautomation.com/t/converting-a-varchar-data-type-to-a-datetime-data-type-created-an-out-of-range-value/34153/17 "2020-05-01T11:35:43Z")
> [@giovannimarco.giorda](#): > > I solved the error in object of this thread directly in SQL query That you can do this for your case _sounds_ good, but is not a generally good solution. It won't be much help when multiple time zones are (or will be) involved, nor can it handle the formatting problems when client and gateway have different locales. Passing data via unambiguous typed binary parameters (JDBC in Ignition) is always best.