db2 在Cognos Report Studio版本10.2.1中格式化日期提示时出现问题

siv3szwd 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(250)

我遇到了一个错误，该错误显示：UDA-SQL-0144检测到算术异常。[IBM][CLI驱动程序][DB2/NT 64]无法使用格式字符串“YYYY-MM-DD”为TIMESTAMP_FORMAT函数解释SQL 20448 N“1993”。SQLSTATE=22007
我不太确定是哪里出了问题...我尝试更改格式，但没有效果。我使用的是to_date表单，但我不确定它是否正确。下面是我使用的SQL select：

select laborcode,
(select sum(workhours) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate','date')#,'YYYY-MM-DD') and to_date(#prompt('EndDate','date')#,'YYYY-MM-DD')) -
(select count(calnum) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate','date')#,'YYYY-MM-DD') and to_date(#prompt('EndDate','date')#,'YYYY-MM-DD') and shiftnum='HOLIDAY') * 8 as stevilo_praznikov_ure,
(select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date(#prompt('StartDate','date')#,'YYYY-MM-DD') and to_date(#prompt('EndDate','date')#,'YYYY-MM-DD')) as delovne_ure,
(select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass.scheduledate between to_date(#prompt('StartDate','date')#,'YYYY-MM-DD') and to_date(#prompt('EndDate','date')#,'YYYY-MM-DD')) as assignment_ure 
from labor l

db2

来源：https://stackoverflow.com/questions/60390540/trouble-with-formatting-date-prompt-in-cognos-report-studio-version-10-2-1

1条答案

按热度按时间

sr4lhrrt1#

我已经解决了提示的问题。我需要做的就是删除提示中的数据类型***“date”***，并将格式模式设置为***YYYY-MM-DD***。感谢各位的帮助，非常感谢！现在选择：

select laborcode,
(select sum(workhours) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate')#,'YYYY-MM-DD') and to_date(#prompt('EndDate')#,'YYYY-MM-DD')) -
(select count(calnum) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate')#,'YYYY-MM-DD') and to_date(#prompt('EndDate')#,'YYYY-MM-DD') and shiftnum='HOLIDAY') * 8 as stevilo_praznikov_ure,
(select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date(#prompt('StartDate')#,'YYYY-MM-DD') and to_date(#prompt('EndDate')#,'YYYY-MM-DD')) as delovne_ure,
(select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass.scheduledate between to_date(#prompt('StartDate')#,'YYYY-MM-DD') and to_date(#prompt('EndDate')#,'YYYY-MM-DD')) as assignment_ure 
from labor l

赞(0）回复(0）举报 2022-11-07

我来回答

db2 在Cognos Report Studio版本10.2.1中格式化日期提示时出现问题

1条答案

相关问题

热门标签

最新问答