# Ignition Report SQL Query in v7.9 Convert to v8.1.25 **URL:** \ **Category:** Ignition\ **Tags:** reporting\ **Created:** [January 10, 2025, 4:43pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735 "2025-01-10T16:43:17Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Jeremy\_Edwards1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jeremy_edwards1/32/71714_2.png) [@Jeremy\_Edwards1](https://forum.inductiveautomation.com/u/Jeremy_Edwards1)\ **Post date:** [January 10, 2025, 4:43pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/1 "2025-01-10T16:43:17Z")
I have a working Data Source in my Ignition v 7.9 that is a SQL Query. I am trying to convert it into a functioning Report in my other Ignition v8.1.25. Below is the script that works in v7.9. ```python > SELECT CONVERT(date,FATS_Land.t_stamp) AS 't_stamp', FATS_Land.Crusher_DailyTotal, FATS_Land.FRE_WIN_UTIL_FATS_EFFLUENT_RANCH_DAILY_FLOW, FATS_Land.Val_City_Byp_Yest_Total, FATS_Land.FRE_WIN_UTIL_FATS_EFFLUENT_POTW_DAILY_FLOW, FATS_Land.DB_Sewer_City_Day_Old FROM FATS_Land WHERE Year(FATS_Land.t_stamp) = ? AND Month(FATS_Land.t_stamp) = ? ``` When I write this query in v8.1.25 it throws the following error. What is the issue with the "?" `> Error running query: SELECT CONVERT(date,t_stamp) AS 't_stamp', Crusher_DailyTotal, FRE_WIN_UTIL_FATS_EFFLUENT_RANCH_DAILY_FLOW, Val_City_Byp_Yest_Total,FRE_WIN_UTIL_FATS_EFFLUENT_POTW_DAILY_FLOW,DB_Sewer_City_Day_Old FROM FATS_Land WHERE YEAR(t_stamp) = ? AND MONTH(t_stamp) = ? Incorrect syntax near '?'.` Any help will be greatly appreciated. ---
**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:** [January 10, 2025, 4:48pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/2 "2025-01-10T16:48:21Z")
Did you use the right type of data source? For `?` placeholders to work you must use ['SQL Query'](https://www.docs.inductiveautomation.com/docs/8.1/ignition-modules/reporting/report-data/sql-query-data-source), not ['Named Query'](https://www.docs.inductiveautomation.com/docs/8.1/ignition-modules/reporting/report-data/named-query-data-source) or ['Basic SQL Query'](https://www.docs.inductiveautomation.com/docs/8.1/ignition-modules/reporting/report-data/basic-sql-query) each of which work subtly differently. ---
**Author:** ![Jeremy\_Edwards1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jeremy_edwards1/32/71714_2.png) [@Jeremy\_Edwards1](https://forum.inductiveautomation.com/u/Jeremy_Edwards1)\ **Post date:** [January 10, 2025, 4:51pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/3 "2025-01-10T16:51:19Z")
Yes, it is set for SQL Query. ![Report SQL Query](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/7/27d07f2809c5bde7d09de092bc73aec4e6c26f89.png) ---
**Author:** ![Jeremy\_Edwards1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jeremy_edwards1/32/71714_2.png) [@Jeremy\_Edwards1](https://forum.inductiveautomation.com/u/Jeremy_Edwards1)\ **Post date:** [January 10, 2025, 4:52pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/4 "2025-01-10T16:52:40Z")
I have tried both "Universal" and "MSSQL", neither of the work. ---
**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:** [January 10, 2025, 4:55pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/5 "2025-01-10T16:55:26Z")
And you're substituting parameters in the expression popups below, right? Can you show us the entire data configuration window as you have it in the software? ---
**Author:** ![Jeremy\_Edwards1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jeremy_edwards1/32/71714_2.png) [@Jeremy\_Edwards1](https://forum.inductiveautomation.com/u/Jeremy_Edwards1)\ **Post date:** [January 10, 2025, 4:56pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/6 "2025-01-10T16:56:49Z")
![Report SQL Query Data Config](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/0/10efd57e53e4f6849de8096a20a2b9e289897edd.png) ---
**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:** [January 10, 2025, 5:00pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/7 "2025-01-10T17:00:27Z")
I don't know what is wrong, but I can't help noting that your WHERE clause is horrible--it can never be optimized with indices, and will become terribly slow as your table grows in size. Your WHERE clause should be comparing the bare `t_stamp` column to lower and upper boundary timestamps. ---
**Author:** ![Jeremy\_Edwards1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jeremy_edwards1/32/71714_2.png) [@Jeremy\_Edwards1](https://forum.inductiveautomation.com/u/Jeremy_Edwards1)\ **Post date:** [January 10, 2025, 5:42pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/8 "2025-01-10T17:42:53Z")
@pturmel , Thank you for your kind words about "my" WHERE clause. 🙂 This was written by someone else about 10 years ago. I am just here getting things migrated to v8. This still does not answer my question about the error on "?". Is there a different syntax to use in v8 ? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [January 10, 2025, 5:45pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/9 "2025-01-10T17:45:01Z")
To expand on Phil's reply, > [@Jeremy\_Edwards1](#): > > `WHERE Year(FATS_Land.t_stamp) = ? AND Month(FATS_Land.t_stamp) = ?` This syntax requires that every single record in the data table must be examined, the YEAR function run and the MONTH function run. Any index on the column can't be used. Instead, for MySQL, use something like this. ```python WHERE FATS_Land.t_stamp BETWEEN STR_TO_DATE(CONCAT(?, '-', ?, '-', 01), '%Y-%m-%d') AND DATEADD( STR_TO_DATE(CONCAT(?, '-', ?, '-', 01), '%Y-%m-%d'), INTERVAL 1 MONTH ) ``` Note that you'll have to pass the year and month values in twice. This way the required date range endpoints are converted to date types just once and the index can now be used to home in on the records of interest very quickly. > **[W3Schools.com](https://www.w3schools.com/sql/func_mysql_str_to_date.asp)** > > W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. ---
**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:** [January 10, 2025, 6:11pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/10 "2025-01-10T18:11:31Z")
> [@Jeremy\_Edwards1](#): > > This still does not answer my question about the error on "?". > > Is there a different syntax to use in v8 ? No, the syntax should be the same. Are you literally copying and pasting the report, or how are you updating/upgrading to 8.1? If you export the project/resource from 7.9 and import into the 8.1 gateway, this is what you get? Reporting is essentially identical between 7.9 and 8.1, so I really don't think the upgrade is implicated here other than coincidentally. ---
**Author:** ![Jeremy\_Edwards1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jeremy_edwards1/32/71714_2.png) [@Jeremy\_Edwards1](https://forum.inductiveautomation.com/u/Jeremy_Edwards1)\ **Post date:** [January 10, 2025, 6:18pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/11 "2025-01-10T18:18:00Z")
@paul-griffith Thank you for confirming that the syntax is the same. I have two installations of Ignition. One is v7.9 and the other is v8.1. I literally copied the query from v7.9 project and pasted it into the v8.1 project. I am NOT upgrading the v7.9 application. I think I can figure it out from here. Kindest regards, Jeremy ---
**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:** [January 10, 2025, 6:22pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-in-v7-9-convert-to-v8-1-25/100735/12 "2025-01-10T18:22:07Z")
Try exporting the resource out of the designer in 7.9 and importing into 8.1, just to see if that works. I would expect copying and pasting to work, but it'd be curious if the import works correctly...