Data Loader and the DateTime/Timestamp data type

Ever wonder why the Salesforce Data Loader isn’t accepting your DateTime/Timestamp format, despite what the documentation says? Most likely not, this case would only be encountered when the user importing the Date and Time values has a non correctly specified format, and the time zone of the user importing the data is different from that which is being specified in the input.

According to the Data Loader Developers Guide [pdf], quoted from the Winter ’11 version:

Date Formats
We recommend you specify dates in the format yyyy-MM-ddTHH:mm:ss.SSS+/-HHmm:

  • yyyy is the four-digit year
  • MM is the two-digit month (01-12)
  • dd is the two-digit day (01-31)
  • HH is the two-digit hour (00-23)
  • mm is the two-digit minute (00-59)
  • ss is the two-digit seconds (00-59)
  • SSS is the three-digit milliseconds (000-999)
  • +/-HHmm is the Zulu (UTC) time zone offset

The following date formats are also supported:

  • yyyy-MM-dd’T’HH:mm:ss.SSS’Z’
  • yyyy-MM-dd’T’HH:mm:ss.SSS Pacific Standard Time
  • yyyy-MM-dd’T’HH:mm:ss.SSSPacific Standard Time
  • yyyy-MM-dd’T’HH:mm:ss.SSS PST
  • yyyy-MM-dd’T’HH:mm:ss.SSSPST
  • yyyy-MM-dd’T’HH:mm:ss.SSS GMT-08:00
  • yyyy-MM-dd’T’HH:mm:ss.SSSGMT-08:00
  • yyyy-MM-dd’T’HH:mm:ss.SSS -800
  • yyyy-MM-dd’T’HH:mm:ss.SSS-800
  • yyyy-MM-dd’T’HH:mm:ss
  • yyyy-MM-dd HH:mm:ss
  • yyyyMMdd’T’HH:mm:ss
  • yyyy-MM-dd
  • MM/dd/yyyy HH:mm:ss
  • MM/dd/yyy

This documentation is not correct in all cases, and sort of correct in some cases (as of Winter ’11 and Spring ’11, using v17 of dataloader.jar for LexiLoader on OS X). Disclaimer: as is the Salesforce way, these “issues” may be fixed in a newer release than the one I used; you should always use the newest version available to you. In fact, some or all of these may already be fixed, given that the version of dataloader.jar I’m using is at least 3 releases out of date.

To thoroughly test this, I set the org to have the MST time zone (GMT -7) from Setup => Company Profile => Company Information. This portion was not required per se, but I was ensuring that this value wasn’t consulted in any case. Next, I created a user with PST time zone (GMT -8) from Setup => My Personal Information => Personal Information. Then I created a custom object with a DateTime custom field, so that I could quickly use data loader for testing various scenarios.

The strategy behind the test is to put a new time zone in (I used EST — GMT -5 in my tests), and confirm that it shows the correct time to the user. If I put in today at 8 a.m. GMT -5 using data loader, the DateTime field should display as 5 a.m. to my user with GMT -8 configured in my time zone. Anything other than 5 a.m. today is plain incorrect.

A few notes in general about the DateTime values set in the data loader source CSV.

  1. If the time portion fails validation (by their seemingly arbitrary validation rule), the time portion is dropped and the date is imported as midnight in the current user’s time zone.
  2. If the time zone portion fails validation or perhaps doesn’t match one of its known time zones, the time zone portion is dropped and the time is imported in the current user’s time zone.
  3. If the SSS section is missing from the time, you will not be able to specify a time zone. When in doubt, append .000.
  4. If spelling the time zone out and it is not an exact match (I tried Easter Standard Time instead of Eastern Standard Time), the results can be unpredictable. My example yielded GMT +5 as a result in this case, and it looks as though this might correspond to Zone E, but I’m not able to reproduce with JUST ‘E’, as mentioned below.
  5. ‘T’ and ‘Z’ should NOT actually have single quotes surrounding them in the data. If ‘T’ does, then the time portion is dropped. If ‘Z’ does, then the time zone portion is dropped. This is consistent with my #1 and #2 findings above.
  6. It doesn’t matter whether or not the DateTime is quote enclosed, which makes sense. This is a function of the CSV parser, and not the data itself (unless of course there is a quote in the data, but if you’re seeing that, you most likely have larger issues…)
  7. The Z in yyyy-MM-dd’T’HH:mm:ss.SSS’Z’ should correspond to Zulu, or Z zone. For the entire list, see Military and Civilian Time Designations. However, this is not correct. When Z is specified in this context, it actually drops the time zone portion, per #2 above. Along the same lines, you cannot use any of the other Time Designations, for example R = EST = GMT -5 will not work in Z’s place — the result will still be the current user’s time zone setting.

Of the above listing of Date Time values with time zone included, only the following formats worked:

  • 2010-02-01T08:00:00.000 Eastern Standard Time
  • 2010-02-01T08:00:00.000Eastern Standard Time
  • 2010-02-01T08:00:00.000 EST
  • 2010-02-01T08:00:00.000EST
  • 2010-02-01T08:00:00.000GMT-05:00
  • 2010-02-01T08:00:00.000-0500
  • 2010-02-01T08:00:00.000GMT-5:00

Whereas the following formats did NOT work, some of which fail time validation and some of which fail time zone validation.

  • 2010-02-01T08:00:00.000 GMT-05:00
  • 2010-02-01T08:00:00.000-05:00
  • 2010-02-01T08:00:00.000GMT-05
  • 2010-02-01T08:00:00.000GMT-5

In summary, there are only a few formats supported by the data loader to correctly insert or update a DateTime field. These formats are a subset of the formats that it purports to support. Use this format to be certain:

This entry was tagged , . Bookmark the permalink.

3 Responses to Data Loader and the DateTime/Timestamp data type

  1. Rick Nelson says:

    First, thank you for publishing this – saved my butt on Sat. during a large conversion. We were getting weird and inconsistent results with Activity records. I used your suggested format and it does work, but I ran into a bizarre twist: even though our client is in Eastern time (we’re in CA), the -0500 offset didn’t work; I found I had to use -1300; then found that it depends on Daylight Savings Time – have to use -1100 during EDT and -1300 during EST!! Makes no sense to me… we have the org on EDT, our user is on EDT, our Dataloaders are set to that ridiculous “Americas/New_York”, etc., so I don’t get what’s going on here. If you have any ideas it would be appreciated. Meanwhile we’re loading 140,000 Tasks with -1100/-1300 offsets… weird!

  2. Ray Dehler says:

    Good to know, thanks!

  3. Pooja says:

    Thanks. This was helpful!

Comments are closed.