You can translate the question and the replies:

ITPilot case function doesn't work fine

Hi! I am working with ITPilot wrapper trying to process output record and create a new field with into a value editor. I have a field date as string and I try to format with two patterns (with and without hours) case max(indexof(Iterator_1_output.date, ":"),0) when 0 then todate("dd/MM/yyyyHH:mm", removewhitespaces(Iterator_1_output.date)) else todate("dd/MM/yyyy", removewhitespaces(Iterator_1_output.date)) end If my input is, for example, "28/07/2018", case statement is evaluating boths parts (then and else) before building the result, and my code never works because you can't parse 28/07/2018 with "dd/MM/yyyyHH:mm". Any Idea? I am working with denodo v6.0 Thanks!
user
20-08-2018 07:47:53 -0400

2 Answers

Hi, In order to avoid this error, I would convert the date field into required date format using the **"FORMATDATE"** function which is used to transform date values into string type values by using a specific format and then I would use the **"TODATE"** function on top of it. For example: * todate("dd/MM/yyyyHH:mm",removewhitespaces(formatdate("dd/MM/yyyyHH:mm", todate("dd/MM/yyyy",<FIELD_NAME>) Since the **"FORMATDATE"** function requires a field of type date as an input parameter, I have used the **"TODATE"** function by passing the date field as string as an input parameter which will convert the text strings representing dates into date-type elements. By using the above-mentioned example in the Case Clause, I was able to retrieve the result as expected. For more information, you can also have a look at the sections [Text Processing Functions](https://community.denodo.com/docs/html/browse/6.0/itpilot/generation_environment/appendix_b_itpilot_functions/text_processing_functions/text_processing_functions#text-processing-functions) and [Date Processing Functions](https://community.denodo.com/docs/html/browse/6.0/itpilot/generation_environment/appendix_b_itpilot_functions/date_processing_functions/date_processing_functions#date-processing-functions) of ITPilot Generation Environment Guide. Hope this helps!
Denodo Team
21-08-2018 05:18:31 -0400
Hi! It doesn't work fine, if I try to execute todate("dd/MM/yyyyHH:mm", removewhitespaces(formatdate("dd/MM/yyyyHH:mm", todate("dd/MM/yyyyHH:mm", Iterator_1_output.date)))) the result is : An error has occurred while evaluating the expression: com.denodo.itp.component.core.evaluator.library.functions.exceptions.ExecutionException: Unparsable date "28/07/2018" with format "dd/MM/yyyyHH:mm" I solved the problem changing case clause order and adding hours and minutes when field is short: todate("dd/MM/yyyy HH:mm", case max(indexof(Iterator_1_output.date, ":"),0) when 0 then concat(Iterator_1_output.date," 00:00") else Iterator_1_output.date end) It works with "28/07/2018" and "28/07/2018 17:50" thanks!
user
21-08-2018 07:15:43 -0400
You must sign in to add an answer. If you do not have an account, you can register here