The datepart hour is not supported by date function dateadd for data type date

Question

While I am running the following query in jasper reports, I got the exception. "net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Activity_Summary". Activity_Summery_Report is my jrxml file name.

The query is

declare @startdate as datetime;
declare @enddate as datetime;
declare @sitegroup as nvarchar(50);
set @startdate = DATEADD(hh, +0, $P{StartDate});
set @enddate = DATEADD(hh, +0, $P{EndDate});
set @sitegroup = 'BBXNCR';
set NOCOUNT ON; -- Added by JC230090: Fixes bug when running query with Jasper

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_invlines]') AND type in (N'U'))
DROP TABLE [dbo].[#temp_invlines]
create table #temp_invlines (site_iid uniqueidentifier, invoice_line_type_iid int,    sub_total float, tax_total float,line_total float, product_type_iid int);
insert into #temp_invlines (site_iid,invoice_line_type_iid, sub_total, tax_total,   line_total, product_type_iid)
select h.site_iid, invoice_line_type_iid, l.sub_total, l.tax_total, l.line_total, l.product_type_iid
from invoice_lines l
    inner join invoices h on l.invoice_iid = h.invoice_iid
where h.invoice_dt >= @startdate and h.invoice_dt < @enddate

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_payment_invoices]') AND type in (N'U'))
DROP TABLE [dbo].[#temp_payment_invoices]
create table #temp_payment_invoices (site_iid uniqueidentifier, inv_amount float);
insert into #temp_payment_invoices (site_iid, inv_amount)
select i.site_iid, i.total
from invoice_payments p
    inner join Invoice_Payment_Invoice_Xref pXref on p.invoice_payment_iid = pXref.invoice_payment_iid
    inner join invoices i on pXref.invoice_iid = i.invoice_iid
where p.payment_dt >= @startdate and p.payment_dt < @enddate

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_promocodes]') AND type in (N'U'))
DROP TABLE [dbo].[#temp_promocodes]
create table #temp_promocodes (site_iid uniqueidentifier, invoice_promo_line_type_iid int, promo_amount float);
insert into #temp_promocodes (site_iid, invoice_promo_line_type_iid, promo_amount)
select h.site_iid, pl.invoice_promo_line_type_iid, pl.amount
from invoice_promo_lines pl
inner join invoices h on pl.invoice_iid = h.invoice_iid
where h.invoice_dt >= @startdate and h.invoice_dt < @enddate



select 
 rtrim(s.site_id) as [Site ID]
 , rtrim(ss.Description) as [Status]
 , @startdate as [Date]
, datename(dw,@startdate) as [DoW]
, rtrim(s.name) as [Store Code]
, rtrim(isnull(sg.Description,'')) as [Site Group]
, rtrim(s.address1) + ', ' + rtrim(s.city) + ', ' + rtrim(s.state) + ' ' +   rtrim(s.Zip) as [Address]

, (select count(*) from invoices h where h.invoice_dt >= @startdate and h.invoice_dt < @enddate and h.site_iid = s.site_iid) as [Trans]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 1) as [Rent Rev]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 2) as [Ext Day Rev]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 0 and l.product_type_iid = 2) as [Used Sell Rev]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 0 and l.product_type_iid <> 2) as [New Sell Rev]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Rev Total]
, (select isnull(sum(tax_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Tax Total]
, (select isnull(sum(line_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Grand Total]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid) /
(select case when count(*) = 0 then 1 else count(*) end from invoices h where h.invoice_dt >= @startdate and h.invoice_dt < @enddate and h.site_iid = s.site_iid)
as [Rev/Trans]
, (select isnull(count(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 1) as [Rent Cnt]
, (select isnull(count(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 2) as [Ext Day Cnt]
, (select isnull(count(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 0 and l.product_type_iid = 2) as [Used Sell Cnt]
, (select isnull(count(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 0 and l.product_type_iid <> 2) as [New Sell Cnt]
, (select isnull(count(promo_amount),0) from #temp_promocodes p where p.site_iid = s.site_iid and p.invoice_promo_line_type_iid = 0) as [CustSrv Promo Cnt]
, (select isnull(sum(promo_amount),0) from #temp_promocodes p where p.site_iid = s.site_iid and p.invoice_promo_line_type_iid = 0) as [CustSrv Promo Amt]
, (select isnull(count(promo_amount),0) from #temp_promocodes p where p.site_iid = s.site_iid and p.invoice_promo_line_type_iid = 1) as [Marketing Promo Cnt]
, (select isnull(sum(promo_amount),0) from #temp_promocodes p where p.site_iid = s.site_iid and p.invoice_promo_line_type_iid = 1) as [Marketing Promo Amt]

, (SELECT  
    isnull(count(*),0)
FROM Invoice_Lines ilrtn 
    INNER JOIN Invoices ihrtn on ilrtn.invoice_iid = ihrtn.invoice_iid 
    inner join customer_ids c on ihrtn.customer_id_iid = c.customer_id_iid
    INNER JOIN Invoices ihrent on ihrtn.customer_id_iid = ihrent.customer_id_iid  
    INNER JOIN Invoice_lines ilrent on ihrent.invoice_iid = ilrent.invoice_iid 
WHERE ihrtn.invoice_dt >= @startdate
    and ihrtn.invoice_dt < @enddate
    and ilrtn.invoice_line_type_iid in (0,2)
    AND ihrent.invoice_dt <= ihrtn.invoice_dt 
    AND ilrent.invoice_line_type_iid in (1) 
    AND ilrent.piece_id = ilrtn.piece_id 
    and ihrtn.site_iid = s.site_iid 
  ) as [Invoices]

, (SELECT  
    isnull(sum(ilrent.line_total + ilrtn.line_total),0)
FROM Invoice_Lines ilrtn 
    INNER JOIN Invoices ihrtn on ilrtn.invoice_iid = ihrtn.invoice_iid 
    inner join customer_ids c on ihrtn.customer_id_iid = c.customer_id_iid
    INNER JOIN Invoices ihrent on ihrtn.customer_id_iid = ihrent.customer_id_iid  
    INNER JOIN Invoice_lines ilrent on ihrent.invoice_iid = ilrent.invoice_iid 
WHERE ihrtn.invoice_dt >= @startdate
    and ihrtn.invoice_dt < @enddate
    and ilrtn.invoice_line_type_iid in (0,2)
    AND ihrent.invoice_dt <= ihrtn.invoice_dt 
    AND ilrent.invoice_line_type_iid in (1) 
    AND ilrent.piece_id = ilrtn.piece_id 
    and ihrtn.site_iid = s.site_iid 
  ) as [Collectable]


, ( select isnull(sum(p.amount),0)
from invoice_payments p
where p.payment_dt >= @startdate and p.payment_dt < @enddate
and p.invoice_payment_iid in (
    select distinct p.invoice_payment_iid
    from invoice_payments p
    inner join Invoice_Payment_Invoice_Xref pXref on p.invoice_payment_iid = pXref.invoice_payment_iid
    inner join invoices i on pXref.invoice_iid = i.invoice_iid
    where p.payment_dt >= @startdate and p.payment_dt < @enddate
    and i.site_iid = s.site_iid
    group by p.invoice_payment_iid, pXref.invoice_iid)
) as [Payments Collected]



from sites s
inner join Site_Statuses_Enum ss on s.site_status_iid = ss.site_status_iid
left outer join Site_SiteGroup_XRef sgx on s.site_iid = sgx.site_iid
left outer join Site_Groups sg on sgx.site_group_iid = sg.site_group_iid

order by s.site_id

drop table #temp_promocodes;
drop table #temp_payment_invoices;
drop table #temp_invlines;

And the exception stack is

Error filling print... 
Error executing SQL statement for : Activity_Summary
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Activity_Summary
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:656)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1196)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:833)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:782)
at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:234)      
at it.businesslogic.ireport.IReportCompiler.run(IReportCompiler.java:947)      
at java.lang.Thread.run(Unknown Source)         Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The datepart hour is not supported by date function dateadd for data type date.      
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)      
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)      
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)      
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)      
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)      
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)      
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)     
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)      
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)      
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135)     
 ... 10 more  
 Print was not filled. Try using an EmptyDataSource...

Please help me anybody how to resolve it. Thanks in advance

Answer 1

This happens because your input parameters are of the data type date, and not datetime. Because the date data type's precision only runs to day level, not anything smaller.

For example, this will fail in SQL Server:

declare @Start as date = '20130201'
declare @End as date = '20130210'
declare @startdate as datetime;
declare @enddate as datetime;
set @startdate = DATEADD(hh, +0, @Start)
set @enddate = DATEADD(hh, +0, @End)

While this will work:

declare @Start as datetime = '20130201'
declare @End as datetime = '20130210'
declare @startdate as datetime;
declare @enddate as datetime;
set @startdate = DATEADD(hh, +0, @Start)
set @enddate = DATEADD(hh, +0, @End)

If you ensure that the parameters supplied by your application (below) are of type datetime and not date, you should get rid of the error.

$P{StartDate}
$P{EndDate}