Hi @Gundappa Pujari , probably in your case, changing to "-" to "/" to ('%m/%d/%Y') will solve it, hope at least

Hello Javier,

Thank you for your email. I have changed "- " to "/" but still it is not working. The code looks correct, not sure why it isn't working :(

CASE WHEN ( ISNULL ( TO_DATE ( "DATE_xlsx_Sheet1"."DATE IN" , FORMAT ('%m/%d/%Y'))) = 1 ) THEN 'INCORRECT' 

ELSE 'CORRECT'

END

FOR NAME, JOHN, SMILE & JIGAR it should show as correct as they have correct date format.

also I identified one thing, while uploading the excel file the DATE IN column value is automatically converting to long time format (SCREEN SHOT 2). I guess this is causing issue.

SCREEN SHOT 2

yes, you are right, TO_DATE should have as an input a string with the requested format. Have you tried while updating the file, change the column format to string:

At least the correct dates should be OK

Hope it helps

Yes, while uploading the file I have converted the column to STRING.

I would then try to transform the file with sql, converting the column date to string, maybe could help.

Thank you for your help. Is there any other way to fix it, apart from transforming the file with SQL ?

Hi Javier,

I got the solution, but that needs manual work i.e. we need to convert the date column in Raw excel to TEXT field before uploading it to CELONIS.

is there any other way that you could think of, if so please help me on this.

Thanks,

Gundappa

👍 great, I think this way or the other (converting to string once imported with sql data job)