So recently we were doing a migration of data (activities) and dates always seem to cause issues whether you’re using Data Loader, DataLoader.io, import wizard, etc. However, there are a couple of ways to do things that we’ve found work a bit better with date formats, a bit friendlier to Salesforce during migration.
First one that I’ve always tried is to add an Excel formula to the CSV (or XLSX file). Just add a column to the right of your date field, then use this formula:
=TEXT(G2, “yyyy-mm-dd hh:MM:ss”)
This would be used where G2 is your date field. Turns out that just by doing “short date” or “long date” in Excel as a column format isn’t always the best and does NOT come in cleanly — usually you get a failure.
The other one is more for the SQL gurus out there, if you’re doing an export from SQL server or something like that. In the past I’ve used this as well:
select convert(VARCHAR, <<DATEFIELD>>, 101)+’T’+convert(VARCHAR, <<TIMEFIELD>>, 108)+’.000Z’ as [Date/Time]
As you can see, not for the faint of heart but it does work well if you have a date and time field separate. If you don’t, you can convert but what’s interesting is that 000Z at the end. For some reason that works very well for timezone types of things. If you do some searching on “Salesforce 000Z” (out there on the web) you’ll see some interesting posts on dealing with time zones, this pesky Z character, etc.
