# SQL Query Error

**URL:** <https://forum.inductiveautomation.com/t/sql-query-error/8753>\
**Category:** Ignition\
**Created:** [July 18, 2014, 1:37pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/8753 "2014-07-18T13:37:37Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ceccontrols](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@ceccontrols](https://forum.inductiveautomation.com/u/ceccontrols)\
**Post date:** [July 18, 2014, 1:37pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/8753/1 "2014-07-18T13:37:37Z")

</div>

I am getting an “error converting varchar to bigint” on my query on tag history. It is happening in the where clause. t\_stamp is a date, intvalue is an bigint, dataintegrity is an int.

Sample of row data and error below.

tagid intvalue floatvalue stringvalue datevalue dataintegrity t\_stamp  
28 920 NULL NULL NULL 192 1405616502411

ERROR [QueryBrowser$6$3-AWT-EventQueue-2] Error running query:  
SELECT t\_stamp, intvalue, dataintegrity  
FROM dbo.sqlt\_data\_1\_2014\_07  
Where t\_stamp \>= ‘{Root Container.Date Range.startDate}’ And t\_stamp \<= '{Root Container.Date Range.endDate}'  
Error converting data type varchar to bigint.  
com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: Error converting data type varchar to bigint.  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.newGatewayException(GatewayInterface.java:313)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:244)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:712)  
at com.inductiveautomation.ignition.designer.querybrowser.QueryBrowser$6.run(QueryBrowser.java:481)  
at java.lang.Thread.run(Unknown Source)  
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type varchar to bigint.  
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)  
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4700)  
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1683)  
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:956)  
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)  
at com.inductiveautomation.ignition.gateway.datasource.DelegatingResultSet.next(DelegatingResultSet.java:419)  
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:143)  
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:76)  
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:376)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)  
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)  
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)  
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)  
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)  
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)  
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)  
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)  
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)  
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)  
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)  
at java.lang.Thread.run(null)

---

<div class="post-metadata">

**Author:** ![drewdin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/drewdin/32/3363_2.png) [@drewdin](https://forum.inductiveautomation.com/u/drewdin)\
**Post date:** [July 18, 2014, 6:17pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/8753/2 "2014-07-18T18:17:02Z")

</div>

what does the DB structure look like?

---

<div class="post-metadata">

**Author:** ![george](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/george/32/14066_2.png) [@george](https://forum.inductiveautomation.com/u/george)\
**Post date:** [July 23, 2014, 2:58pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/8753/3 "2014-07-23T14:58:50Z")

</div>

Looks like you are probably using a date string in your query, but the Ignition History tables store the date/time numerically rather than in a DateTime field.

---

<div class="post-metadata">

**Author:** ![ceccontrols](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@ceccontrols](https://forum.inductiveautomation.com/u/ceccontrols)\
**Post date:** [July 23, 2014, 4:36pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/8753/4 "2014-07-23T16:36:26Z")

</div>

The query had to be modified to put the date in the correct format:

SELECT intvalue, dateadd(s,t\_stamp/1000,‘1970-01-01 00:00:00’) as t\_stamp from MyDB.dbo.sqlt\_data\_1\_2014\_07  
where cast(dateadd(s,t\_stamp/1000,‘1970-01-01 00:00:00’) as datetime) \>= ‘{Root Container.Date Range.startDate}’  
and cast(dateadd(s,t\_stamp/1000,‘1970-01-01 00:00:00’) as datetime) \<= ‘{Root Container.Date Range.endDate}’  
and tagid = 29

---

<div class="post-metadata">

**Author:** ![Lalillo1](https://avatars.discourse-cdn.com/v4/letter/l/a6a055/32.png) [@Lalillo1](https://forum.inductiveautomation.com/u/Lalillo1)\
**Post date:** [July 7, 2018, 12:56am UTC](https://forum.inductiveautomation.com/t/sql-query-error/8753/5 "2018-07-07T00:56:08Z")

</div>

Examples:

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/e/e2247c474262627ac3555c3f9af3e27a59b52907.png)

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/61e72e5b6fc700eca99337e28d9c189c1fdf2fa5.png)

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2f9578ec5bf4ec158774528519ffbb91b04f87f6.png)

---

<div class="post-metadata">

**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:** [July 7, 2018, 2:48am UTC](https://forum.inductiveautomation.com/t/sql-query-error/8753/6 "2018-07-07T02:48:02Z")

</div>

Missed this back in 2014… If you want your queries to run efficiently, convert your parameters to the data type needed in the DB, don’t make the DB convert every t\_stamp value to a datetime. Simply use [toMillis()](https://docs.inductiveautomation.com/display/DOC79/toMillis) on the start and end date in custom (Long) properties, and pass _those_ to the WHERE clause.