JasperReports SQL execution failure with data type mismatch

Data and Databases
1

I’m getting a data type conflict error when trying to run my report in JasperReports. The error message displays ‘Operand type clash: date is incompatible with int’ yet the same SQL query executes perfectly when I run it directly in SQL Server Management Studio.

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Monthly_Sales_Report
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: date is incompatible with int

Here’s how I’ve set up my parameter and the query:

<parameter name="ReportDate" isForPrompting="true" class="java.util.Date">
    <defaultValueExpression><![CDATA[new Date(System.currentTimeMillis())]]></defaultValueExpression>
</parameter>

<queryString><![CDATA[
declare @begin_date as datetime;
declare @final_date as datetime;
declare @location_group as nvarchar(50);
set @begin_date = $P{ReportDate}-1;
set @final_date = $P{ReportDate};
set @location_group = 'REGION1';

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_orders]') AND type in (N'U'))
DROP TABLE [dbo].[#temp_orders]

create table #temp_orders (location_id uniqueidentifier, order_type_id int, subtotal_amount float, tax_amount float, total_amount float, item_category_id int);

insert into #temp_orders (location_id, order_type_id, subtotal_amount, tax_amount, total_amount, item_category_id)
select o.location_id, order_type_id, od.subtotal_amount, od.tax_amount, od.total_amount, od.item_category_id
from order_details od
inner join orders o on od.order_id = o.order_id
where o.order_date >= @begin_date and o.order_date < @final_date

select 
loc.location_code as [Location],
@begin_date as [Period],
(select count(*) from orders o where o.order_date >= @begin_date and o.order_date < @final_date and o.location_id = loc.location_id) as [Total Orders],
(select isnull(sum(subtotal_amount),0) from #temp_orders t where t.location_id = loc.location_id and t.order_type_id = 1) as [Service Revenue]
from locations loc
where loc.status = 'Active'
order by loc.location_code

drop table #temp_orders
]]></queryString>

It’s puzzling because this SQL runs fine in SSMS. Has anyone faced a similar issue with date parameters in JasperReports? What might be causing this data type mismatch?

2

I encountered this exact issue last year and it turned out to be related to how JasperReports handles date arithmetic in SQL Server. The problem lies in the line set @begin_date = $P{ReportDate}-1; where you’re performing arithmetic directly on the parameter. JasperReports sometimes passes date parameters in a format that SQL Server interprets inconsistently during arithmetic operations. I resolved this by explicitly converting the parameter to datetime before performing any operations. Try modifying your variable assignments to set @begin_date = DATEADD(day, -1, CAST($P{ReportDate} AS datetime)); and set @final_date = CAST($P{ReportDate} AS datetime); instead of using direct arithmetic. This ensures SQL Server properly handles the date parameter regardless of how JasperReports passes it to the query engine.

3

interesting issue! are you sure the parameter is actually being passed as a date type? sometimes jasperreports converts java.util.Date to different formats depending on jdbc driver version. what happens if you add some debug output to see what $P{ReportDate} actually contains when it hits sql server? might be worth checking the jasper logs too

4

had similiar problem few months back. try wrapping your parameter in quotes when setting the variables like set @begin_date = '$P{ReportDate}'-1; instead of direct assignment. jasperreports sometimes doesnt handle the parameter substitution correctly without quotes, especially with sql server datetime operations.