数据类型日期的日期功能不支持datepart小时 [英] The datepart hour is not supported by date function dateadd for data type date
问题描述
虽然我在jasper报告中运行以下查询,但出现了异常. "net.sf.jasperreports.engine.JRException:为以下项目执行SQL语句时出错:Activity_Summary". Activity_Summery_Report是我的jrxml文件名.
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.
查询是
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;
异常堆栈是
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
推荐答案
之所以会发生这种情况,是因为您的输入参数的数据类型为date
,而不是datetime
.因为date
数据类型的精度只能达到日级水平,所以不会更小.
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.
例如,这将在SQL Server中失败:
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)
这将起作用:
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)
如果确保您的应用程序(以下)提供的参数的类型为datetime
而不是date
,则应消除此错误.
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}
这篇关于数据类型日期的日期功能不支持datepart小时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!