{"id":2723,"date":"2022-08-30T15:27:14","date_gmt":"2022-08-30T15:27:14","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2014\/02\/08\/the-datepart-hour-is-not-supported-by-date-function-dateadd-for-data-type-date-collection-of-common-programming-errors\/"},"modified":"2022-08-30T15:27:14","modified_gmt":"2022-08-30T15:27:14","slug":"the-datepart-hour-is-not-supported-by-date-function-dateadd-for-data-type-date-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2022\/08\/30\/the-datepart-hour-is-not-supported-by-date-function-dateadd-for-data-type-date-collection-of-common-programming-errors\/","title":{"rendered":"The datepart hour is not supported by date function dateadd for data type date-Collection of common programming errors"},"content":{"rendered":"

While I am running the following query in jasper reports, I got the exception. “net.sf.jasperreports.engine.JRException:\u00a0Error\u00a0executing\u00a0SQL\u00a0statement\u00a0for\u00a0:\u00a0Activity_Summary”. Activity_Summery_Report is my jrxml file name.<\/p>\n

The query is<\/p>\n

declare @startdate as datetime;\ndeclare @enddate as datetime;\ndeclare @sitegroup as nvarchar(50);\nset @startdate = DATEADD(hh, +0, $P{StartDate});\nset @enddate = DATEADD(hh, +0, $P{EndDate});\nset @sitegroup = 'BBXNCR';\nset NOCOUNT ON; -- Added by JC230090: Fixes bug when running query with Jasper\n\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_invlines]') AND type in (N'U'))\nDROP TABLE [dbo].[#temp_invlines]\ncreate table #temp_invlines (site_iid uniqueidentifier, invoice_line_type_iid int,    sub_total float, tax_total float,line_total float, product_type_iid int);\ninsert into #temp_invlines (site_iid,invoice_line_type_iid, sub_total, tax_total,   line_total, product_type_iid)\nselect h.site_iid, invoice_line_type_iid, l.sub_total, l.tax_total, l.line_total, l.product_type_iid\nfrom invoice_lines l\n    inner join invoices h on l.invoice_iid = h.invoice_iid\nwhere h.invoice_dt >= @startdate and h.invoice_dt < @enddate\n\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_payment_invoices]') AND type in (N'U'))\nDROP TABLE [dbo].[#temp_payment_invoices]\ncreate table #temp_payment_invoices (site_iid uniqueidentifier, inv_amount float);\ninsert into #temp_payment_invoices (site_iid, inv_amount)\nselect i.site_iid, i.total\nfrom invoice_payments p\n    inner join Invoice_Payment_Invoice_Xref pXref on p.invoice_payment_iid = pXref.invoice_payment_iid\n    inner join invoices i on pXref.invoice_iid = i.invoice_iid\nwhere p.payment_dt >= @startdate and p.payment_dt < @enddate\n\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_promocodes]') AND type in (N'U'))\nDROP TABLE [dbo].[#temp_promocodes]\ncreate table #temp_promocodes (site_iid uniqueidentifier, invoice_promo_line_type_iid int, promo_amount float);\ninsert into #temp_promocodes (site_iid, invoice_promo_line_type_iid, promo_amount)\nselect h.site_iid, pl.invoice_promo_line_type_iid, pl.amount\nfrom invoice_promo_lines pl\ninner join invoices h on pl.invoice_iid = h.invoice_iid\nwhere h.invoice_dt >= @startdate and h.invoice_dt < @enddate\n\n\n\nselect \n rtrim(s.site_id) as [Site ID]\n , rtrim(ss.Description) as [Status]\n , @startdate as [Date]\n, datename(dw,@startdate) as [DoW]\n, rtrim(s.name) as [Store Code]\n, rtrim(isnull(sg.Description,'')) as [Site Group]\n, rtrim(s.address1) + ', ' + rtrim(s.city) + ', ' + rtrim(s.state) + ' ' +   rtrim(s.Zip) as [Address]\n\n, (select count(*) from invoices h where h.invoice_dt >= @startdate and h.invoice_dt < @enddate and h.site_iid = s.site_iid) as [Trans]\n, (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]\n, (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]\n, (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]\n, (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]\n, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Rev Total]\n, (select isnull(sum(tax_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Tax Total]\n, (select isnull(sum(line_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Grand Total]\n, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid) \/\n(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)\nas [Rev\/Trans]\n, (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]\n, (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]\n, (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]\n, (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]\n, (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]\n, (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]\n, (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]\n, (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]\n\n, (SELECT  \n    isnull(count(*),0)\nFROM Invoice_Lines ilrtn \n    INNER JOIN Invoices ihrtn on ilrtn.invoice_iid = ihrtn.invoice_iid \n    inner join customer_ids c on ihrtn.customer_id_iid = c.customer_id_iid\n    INNER JOIN Invoices ihrent on ihrtn.customer_id_iid = ihrent.customer_id_iid  \n    INNER JOIN Invoice_lines ilrent on ihrent.invoice_iid = ilrent.invoice_iid \nWHERE ihrtn.invoice_dt >= @startdate\n    and ihrtn.invoice_dt < @enddate\n    and ilrtn.invoice_line_type_iid in (0,2)\n    AND ihrent.invoice_dt = @startdate\n    and ihrtn.invoice_dt < @enddate\n    and ilrtn.invoice_line_type_iid in (0,2)\n    AND ihrent.invoice_dt = @startdate and p.payment_dt < @enddate\nand p.invoice_payment_iid in (\n    select distinct p.invoice_payment_iid\n    from invoice_payments p\n    inner join Invoice_Payment_Invoice_Xref pXref on p.invoice_payment_iid = pXref.invoice_payment_iid\n    inner join invoices i on pXref.invoice_iid = i.invoice_iid\n    where p.payment_dt >= @startdate and p.payment_dt < @enddate\n    and i.site_iid = s.site_iid\n    group by p.invoice_payment_iid, pXref.invoice_iid)\n) as [Payments Collected]\n\n\n\nfrom sites s\ninner join Site_Statuses_Enum ss on s.site_status_iid = ss.site_status_iid\nleft outer join Site_SiteGroup_XRef sgx on s.site_iid = sgx.site_iid\nleft outer join Site_Groups sg on sgx.site_group_iid = sg.site_group_iid\n\norder by s.site_id\n\ndrop table #temp_promocodes;\ndrop table #temp_payment_invoices;\ndrop table #temp_invlines;\n<\/code><\/pre>\n
And the exception stack is<\/p>\n
Error filling print... \nError executing SQL statement for : Activity_Summary\nnet.sf.jasperreports.engine.JRException: Error executing SQL statement for : Activity_Summary\nat net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)\nat net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:656)\nat net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588)\nat net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1196)\nat net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:833)\nat net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:782)\nat net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63)\nat net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402)\nat net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:234)      \nat it.businesslogic.ireport.IReportCompiler.run(IReportCompiler.java:947)      \nat 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.      \nat com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)      \n at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)      \nat com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)      \nat com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)      \nat com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)      \nat com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)      \nat com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)     \nat com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)      \nat com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)      \nat net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135)     \n ... 10 more  \n Print was not filled. Try using an EmptyDataSource...\n<\/code><\/pre>\n
Please help me anybody how to resolve it. Thanks in advance<\/p>\n
    \n
  1. \n
    This happens because your input parameters are of the data type date<\/code>, and not datetime<\/code>. Because the date<\/code> data type’s precision only runs to day level, not anything smaller.<\/p>\n
    For example, this will fail in SQL Server:<\/p>\n
    declare @Start as date = '20130201'\ndeclare @End as date = '20130210'\ndeclare @startdate as datetime;\ndeclare @enddate as datetime;\nset @startdate = DATEADD(hh, +0, @Start)\nset @enddate = DATEADD(hh, +0, @End)\n<\/code><\/pre>\n
    While this will work:<\/p>\n
    declare @Start as datetime = '20130201'\ndeclare @End as datetime = '20130210'\ndeclare @startdate as datetime;\ndeclare @enddate as datetime;\nset @startdate = DATEADD(hh, +0, @Start)\nset @enddate = DATEADD(hh, +0, @End)\n<\/code><\/pre>\n
    If you ensure that the parameters supplied by your application (below) are of type datetime<\/code> and not date<\/code>, you should get rid of the error.<\/p>\n
    $P{StartDate}\n$P{EndDate}\n<\/code><\/pre>\n<\/li>\n<\/ol>\n
    Originally posted 2014-02-08 03:35:43. <\/small><\/p>","protected":false},"excerpt":{"rendered":"
    While I am running the following query in jasper reports, I got the exception. “net.sf.jasperreports.engine.JRException:\u00a0Error\u00a0executing\u00a0SQL\u00a0statement\u00a0for\u00a0:\u00a0Activity_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; […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2723","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/2723","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=2723"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/2723\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=2723"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=2723"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=2723"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}