Convert to Date

Hi,

Could you please give us an example showing what a wrong Barcode looks like? If you example the total length of barcodes with the wrong format is longer than of correctly formatted Bar codes, you could just use a Case when statement.

It would look like this:

CASE WHEN LEN(SUBSTRING(BSEG.ZUONR, 0, 9)) =

THEN TO_TIMESTAMP(SUBSTRING(BSEG.ZUONR,0,2)||.||SUBSTRING(BSEG.ZUONR,2,2)||.||20||SUBSTRING(BSEG.ZUONR, 4, 2), DD.MM.YYYY)

ELSE NULL

END

Note: The 9 in SUBSTRING(BSEG.ZUONR, 0, 9) can be replaced with any other number provided its larger than length of your correctly formatted barcodes. Substring is just used to turn the barcode into a string so that the length function can be used.

Also as a quick aside SUBSTRING(BSEG.ZUONR, 4, 2) gets 2 letters from the string in BSEG.ZUONR starting with the 5th one, so the format DD.MM.YY would fit the number of inputs your giving to TO_TIMESTAMP. Furthermore, if your barcode doesnt contain . separators then just remove them from the format, i.e. DDMMYY.

I hope this helps, if not please get back to us with more information.

Best Wishes,

Calandra

Thank you very much - I will adjust the formula as per your suggestions. Regarding

the type of issues with barcode, it is mostly related with incorrect OCR recognition. Sometimes for example instead of 1(digit 1) system writes down l (letter L). So I believe a formula would be needed which for example in case of error puts NULL, in case of correct form it remaps to timestam

UPDATE:

I have also tried with input having correct format, however for some reason I get an error that day value exceeds 1-31. I cannot figure out the mistake in th fomula. Please kindly help. Thank you

pConvert to Date.docx (76.9 KB)

Hey,

Lets first try simplifying your formula a bit. As Calandra already mentioned, another date format works better in your case so your to_timestamp formula should more look like this:

TO_TIMESTAMP(CONCAT(SUBSTRING(BSEG.ZUONR,0,4),20,SUBSTRING(BSEG.ZUONR,4,2)),DDMMYYYY)

I would imagine that testing whether or not that string is date could work like this (this formula tests whether or not the date lies between 01.01.2000 and 31.12.2020 although it is not the nicest variant I think):

CASE WHEN

STR_TO_INT(CONCAT(SUBSTRING(BSEG.ZUONR,0,4),20,SUBSTRING(BSEG.ZUONR,4,2))) > 1012000 AND STR_TO_INT(CONCAT(SUBSTRING(BSEG.ZUONR,0,4),20,SUBSTRING(BSEG.ZUONR,4,2))) <

31122020

THEN

TO_TIMESTAMP(CONCAT(SUBSTRING(BSEG.ZUONR,0,4),20,SUBSTRING(BSEG.ZUONR,4,2)),DDMMYYYY)

ELSE NULL

END

The next thing you should test for is looking at the null values (your not converted dates) and think about why and where they are converted wrongly. Maybe a leading 0 is missing , making the statement give out your error mentioned in the Word Document when the string is like 90319 (09.03.2019). This could easily be fixed by concatenating a leading zero when your string has e.g. LEN(BSEG.ZUONR with a leading 0) - 1.

Best Wishes,

Benedict

Thank you for the answer. I have tried with adjusted

formulaSUBSTRING(BSEG.ZUONR,0,4)||20||(SUBSTRING(BSEG.ZUONR,4,2)) and selected one single case ID where there should be no issue with formating (no zero at the beginning etc). For some reason when trying to convert to date, it still populates an error that values are outside of possible period (even though montth is 12

Convert to Date2.docx (68.3 KB)

Hi,

could you please provide us the celonis version you are using?

Are you sure that the BKPF.BELNR you use in the CASE WHEN condition is unique (that is, are you sure that only 1 row in the CASE WHEN result is not NULL?) I think the Month number out of range error comes from a different value in that column. Could you please check this again?

Thanks and best regards

David