Day of month value is out of range

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.

Hi @soppeneer

The issue is that the TO_TIMESTAMP is still executed on all inputs, doesn’t matter if you have the CASE WHEN around it or not. What you need to do is, you have to remove the invalid strings already in the input to TO_TIMESTAMP:

COALESCE (
TO_TIMESTAMP ( CASE WHEN <%=DateDeliveryOld%> LIKE '20%' THEN  <%=DateDeliveryOld%> ELSE NULL END ), 
PU_FIRST("EKPO", "EKET"."EINDT")
)

I would assume that this should work.

Best,
David

@d.becher Thanks for your feedback. I wasn’t familiar with COALESCE, but it seems like a good fit. Unfortunately when trying your solution, I’m getting the following error. Should I include another validation?

TABLE (COALESCE(TO_TIMESTAMP(CASE WHEN NaN LIKE ‘20%’ THEN NaN ELSE NULL END), PU_FIRST(“EKPO”, “EKET”.“EINDT”)) AS “”) LIMIT 50 OFFSET 0 is not a valid PQL expression: Encountered " “(” “( “” at line 1, column 17. Was expecting one of: “FORMAT” … “/”” … <S_IDENTIFIER> … “,” … “)” … “||” … “” … <S_IDENTIFIER> …

Hi @soppeneer

sorry, seems like I forgot to add the pattern in the TO_TIMESTAMP function:

COALESCE (
TO_TIMESTAMP ( CASE WHEN <%=DateDeliveryOld%> LIKE '20%' THEN  <%=DateDeliveryOld%> ELSE NULL END,  'YYYYMMDD' ), 
PU_FIRST("EKPO", "EKET"."EINDT")
)

But it also seems like your variable has the content “NaN”; I would expect it to be a string column.

Best
David

@d.becher The DeliveryDateOld variable is a static value defined as follows which can return a blank value which I suppose returns the NaN:

‘’ || PU_LAST(“EKPO”, “_CEL_P2P_ACTIVITIES”.“CHANGED_FROM”, “_CEL_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘Change Delivery Date on Schedule Line’ AND “_CEL_P2P_ACTIVITIES”.“EVENTTIME” < {D’2021-01-01’})

@soppeneer Then you should check what the value of your variable is and also exclude this in the CASE WHEN. However, your variable will only return one single value, so I do not understand your use case, because if this single value is NaN, it will always be NaN. But this problem should have occurred before in your original query I would assume.

Best
David

Your suggestion worked after I changed the variable from static value to text/replacement.

Variable DateDeliveryOld (Text/Replacement):

‘’ || PU_LAST(“EKPO”, “_CEL_P2P_ACTIVITIES”.“CHANGED_FROM”, “_CEL_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘Change Delivery Date on Schedule Line’ AND “_CEL_P2P_ACTIVITIES”.“EVENTTIME” < {D’2021-01-01’})

Formula Date_Delivery_Old:

COALESCE(TO_TIMESTAMP(CASE WHEN <%=DateDeliveryOld%> LIKE ‘20%’ THEN <%=DateDeliveryOld%> END, ‘YYYYMMDD’), PU_FIRST(“EKPO”, “EKET”.“EINDT”))

Thanks!