Unable to map datetime field

[itsme]

I am trying to map a field with date time value (2022/11/5 16:8:9) with a filed designed as DateTime. I tried to map it directly and got error in the loading.

Then I am trying to use the Date function date(purchase_date,"yyyy/M/d H:m:s") and it is throwing an error in the validation itself. This time the error is as below

Error transforming data for required destination path timestamp. Details: com.adobe.aep.aexl.jexl.AexlScriptEngine$AepJexlEngine.parse@1:5 date

AEP Schema

Error:

For a different schema with DOB, I tried the date function and got the error. Then as the data was just yyyy/M/d format, I mapped the field directly and it worked fine. But it seems in this case as it has H:m:s part and it is in 24 hour format, it is not accepting direct mapping. Below are the errors I got when tried without calculated field

Parsing errors occurred when converting or validating the data. Successfully ingested 0 rows, review the associated diagnostic files for additional details.

Purhcase%20History%201.csv	INGEST-1555-400	timestamp	Either a required field timestamp of type: timestamp is missing or has a value of null. Add the required field to the input row and try again.
Purhcase%20History%201.csv	MAPPER-3700-199	timestamp	Error transforming data for required destination path timestamp. Details: Unable to convert 2021/8/11 21:44:51 to schema type DATE_TIME. Try using date function with format specifier

Sample data for the row

FIELD	VALUE
purchase_id	2bfd01fa-42fb-df42-85e7-f25e62490b04
ecid	239959e1-e557-4a70-a313-324becce24fe
mdm_id	e4404078-421d-4554-93f1-5589a7605592
product_name	Clean & Clear
product_id	3560
quantity	76.68
price	9.123911198
city	Las Vegas
state	Louisiana
purchase_date	2021/12/15 5:25:16

I am pretty sure I am missing something

BTW, this is mock data prepared - Vegas is not in Louisiana you know 

[Anuhya-Y]

@itsme 

Please check list of dateformats supported by AEP platform https://experienceleague.adobe.com/docs/experience-platform/data-prep/data-handling.html?lang=en 
Failure reason could be the incoming date format “yyyy/M/d H:m:s” is not supported by AEP.
Try below formats

date("2022/11/5 16:08:09","yyyy/MM/dd HH:mm:ss")
date("2021/12/15 05:25:16","yyyy/MM/dd HH:mm:ss")

BTW, Vegas is Vegas , least bothered about the state 

[itsme]

Thanks for your reply. As my data was not having "0" prefixed for those with single digit value, I tried with "yyyy/M/d" after I got the initial error and it worked with DOB.

After looking at specific rows, I found the issue is with time in my data. I found records with "2018/10/21 24:51:4". As hour can't be above 23, AEP had the rights to mark is as error. But I was expecting this to come after the load and I kept 5% threshold for error. I think what AEP is doing in this case is actually validating the data (probably few top rows?) and not letting me go further.

The screenshot below shows an empty row in preview and that record has the 24 as hour I saw in the file.

[Anuhya-Y]

@itsme 

Please make sure the date format of incoming data/file should be consistent.

Preview UI will not show all the rows, so it may not be helpful in identifying the anomaly data completely.  Please enable error diagnostics and load data . This will help to download all the rows which are not loaded into dataset as expected because of format issues etc.

Enable error diagnostics while loading data: https://experienceleague.adobe.com/docs/experience-platform/ingestion/batch/partial.html?lang=en

API to download error details : https://experienceleague.adobe.com/docs/experience-platform/ingestion/quality/error-diagnostics.html...

[Umamaheswari_Yakkala · Accepted solution]

When I had a similar issue, I changed the date format and it was resolved.

I have updated the format like this

Date : YYYY-mm-dd (2022-10-14)

DateTime:2022-10-17T09:39:31.019278Z

Thank You,

UMa