2

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='...'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='...'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
George
  • 124
  • 1
  • 12
  • The `to_date()` function is a synonym for `timestamp_format()`. The value entered to the prompt(s) must follow the specified pattern (DD.MM.YYYY). Otherwise Db2 will throw an exception. – mao Feb 25 '20 at 09:12
  • Hi @mao thanks for the answer! I'm aware oh that the entered prompts must follow the pattern. In my data base is the pattern (YYYY.MM.DD) and at the fist time I've used this patter and didn't worked either. It just keep telling me this error over and over again. So if I use the pattern (YYYY.MM.DD) it says the same error but in the ending: **cannot be interpreted using format string "YYYY.MM.DD"**. I'll be really greateful if you could help me with that. Cheers – George Feb 25 '20 at 09:28
  • Can you try a format of YYYY-MM-DD to see what happens? – Daniel Wagemann Feb 25 '20 at 15:59
  • Hi @Daniel. I've tried now the pattern YYYY-MM-DD, but the error is still here. It now says in the ending: **cannot be interpreted using format string "YYYY-MM-DD"**. Do I need to put some expressions into my query **Filter details**? I've tried an expression like that: **[laborcode] >= ?StartDate?** and the other one **[laborcode] <= ?EndDate?** but it was meaningless, because laborcode is just in style of name.surname of the worker. It went over without an error but there were no values in the chart. I have put for all 3 data items except for laborcode aggregate function so it should work. – George Feb 26 '20 at 06:47
  • Is it possible Workdate could be zero? Can you test and verify all workdate values are valid dates? – VAI Jason Feb 26 '20 at 17:12
  • 1
    Hi @VAIJason. I've checked all three dates: workdate, startdate and scheduledate. Only the scheduledate has a few rows with scheduledate = null. The other two columns are full of data. If I run the select in SQuirreL with parameters like: **:StartDate** and **:EndDate** and give them a range, the select works perfectly. – George Feb 27 '20 at 06:44

1 Answers1

3

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='...'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='...'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
George
  • 124
  • 1
  • 12