'Trouble with formatting date prompt in Cognos Report Studio Version 10.2.1

I'm having trouble with an error that says: UDA-SQL-0144 An arithmetic exception was detected.[IBM][CLI Driver][DB2/NT64] SQL20448N "1993" cannot be interpreted using format string "YYYY-MM-DD" for the TIMESTAMP_FORMAT function. SQLSTATE=22007
I'm not really sure what's wrong...I tried to change the format but it didn't helped. I'm using to_date form, but I'm not sure if it is the correct one. Here is my SQL select that I'm using:

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


Solution 1:[1]

I've solved the problem with the prompts. All I needed to do was removing the data type "date" that I had in my prompts and set the format pattern to YYYY-MM-DD. Thanks guys for all your help, much appreciate! The select now:

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1