I have a variable DateDeliveryOld that takes the last old value from the Delivery Date field, which is in String format (e.g. ‘20210211’). Then I have a formula that should either output the DateDeliveryOld value in date format (TO_TIMESTAMP…), else the current Delivery Date (EKET.EINDT):
CASE
WHEN <%=DateDeliveryOld%> IS NOT NULL
THEN CASE
WHEN <%=DateDeliveryOld%> LIKE ‘20%’
THEN TO_TIMESTAMP(<%=DateDeliveryOld%>, ‘YYYYMMDD’)
ELSE PU_FIRST(“EKPO”, “EKET”.“EINDT”)
END
ELSE PU_FIRST(“EKPO”, “EKET”.“EINDT”)
END
Unfortunately the result I get is the error “Day of month value is out of range 1…31”. I thought I already prevented this by taking out null values and invalid dates (not starting with year 20…).
I exported the data and converted to date in Excel, and following are the invalid dates. I would expect these to be captured in above formula. Is there anything else I should include in the formula to obtain valid dates?
Date_Delivery_old Case count
3256711
00000000 1
22010201 1
22010205 1
22020308 1
22021120 1
30300823 1
My Celonis version is 4.5.0.