ValidationError (Invalid Date) when importing a CSV to a Data Extension with a Date type field

Question

I have extracted the _open Data View from an old instance and tried to import the 139K CSV records into a Data Extension in the new instance.

However, I got a problem with the "EventDate" field when trying to import the CSV file into the Data Extension: 19K out of the 139K had a "Invalid Date" Validation Error:

Results:

Inserted: 119564

ValidationErrors:

Invalid Date: 19764

I could import successfully the 139K records into a similar Data Extension where the DateEvent field was a Text type instead of a Date type. But I need to have all the records with a Date type for the EventDate field.

I don´t understand why this is happening. All the records should have a valid Date since they were imported directly from the Data View.

What could I do to get all the records imported into a Data Extension with a Date type EventDate?

Thanks, Javi

Answer 1

Check the date format in the file. I bet they're in some specific format and you're selecting another when importing. The ones that got to import successfully were those whose format Marketing Cloud was able to parse: if you have 01-06-2020, it could be June 1st or January 6th, but both are valid dates (they import fine); but if you have 06-13-2020 in your file and you selected "Spanish (Spain)" date format, it'll fail, since there's no such a month as 13.

Check this out: http://help.marketingcloud.com/contentassets/07cc53e235984271b7f0d12c02e1a5d8/dateformats_bylocale.txt

Regards,

AGMH.