query regarding datetime datatype field

[Shaini_Gupta]

Hi,

I have been trying to ingest data in field which is datetime datatype.Below is my query around it.

If we have a field which is of datetime datatype. So is there any standard format in which we have to ingest this field's data.

Additionally is it mandate to convert this fields data to UTC timezone.By default in which timezone this data is.

Thanks 

[abhinavbalooni]

Hey @Shaini_Gupta 

A similar query has already been addressed a few months back. Have a look here: https://experienceleaguecommunities.adobe.com/t5/adobe-experience-platform/source-date-transformatio...

If you still have doubts after reading the above solution, feel free to put in your query on this thread.

Cheers,

Abhinav

[Shaini_Gupta]

Hi @abhinavbalooni ,

Thanks for quick turnaround.

I have additional query on this we have data coming external source in which we have dob coming as "17-12-1974" however client is expecting as to do transformation and show person.birthDate in AEP as date(dob, "MM/dd/yyyy") so I tried this however this transformation is not showing any result in preview mode.

As a work around I tried with "format(date(dob), "MM/dd/yyyy")" and we getting desired formatting however when I map this to person.birthDate(date column) its not showing any result however its working fine when we map it to any string column.Can you please help me with this.

Additionally any recommendation on this is it mandatory that to change datetime columns to UTC time zone.

Thanks

[brekrut · Accepted solution]

can you provide the format of the field which you are attempting to map the string value into?

The date function will convert a date string into a ZonedDateTime object (ISO 8601 format).

https://experienceleague.adobe.com/docs/experience-platform/data-prep/functions.html?lang=en#date-an...

[Shaini_Gupta]

Hi @brekrut ,

Data transformation result of  "format(date(dob), "MM/dd/yyyy")" for eg "12/17/1974" is what I am trying to map to string column.

However I want "date(dob ,"MM/dd/yyyy") so that result is in date format as I have to map it to date column.

Thanks

[arpan-garg]

Hello @Shaini_Gupta  - I may not fully understand your question, but I'll do my best to help clarify.

As I understood, you have an attribute called person.birthDate in date format, and you're attempting to map data from your source system to this field. You mentioned trying to convert "17-12-1974" using date(dob, "MM/dd/yyyy"), but it didn't work. I believe the issue lies in attempting to convert the day (17) to a month, which is incorrect. Instead, you should use date("17-12-1974", "dd-MM-yyyy") to correctly convert the date to the desired format.

I hope this explanation addresses your query.

Thanks,

[NimashaJain]

@Shaini_Gupta Did you find the suggested solutions helpful? It would be great if you can  mark the answer as correct for posterity. If you have found out solution yourself, share it with wider audience in the community.