Wednesday, 31 August 2011

Date format while uploading data in salesforce

I am back to text my knowledge to share with world.
These days I was much involved in data loading part and here I was doing a minor mistake, but each time this minor mistake forced me to re-import .

In Apex data loader setting we have a check box  "Use European date format(dd/mm/yyyy)", true value for this checkbox allowed you to insert the date in dd/mm/yyyy format.

Now may be you are thinking that what is new here and what is the need to write a blog on this as this looks very common.

Now the picture starts from here :
As you have marked the checkbox as true and you are planning to insert the date in the same format, but by mistake date is still in MM/DD/YYYY format in your csv file.

What will happen now? any guess?
Will data loader insert these records or it's going to throw an error.
and if the record inserted, will date filed be populated ?

No more questions o/w people will leave this topic :P.

So here is the answer :
Data loader will not throw any error, it will insert the date in date field, but date field value will be distorted.

So here is the example:
Suppose I have this setting checked in my Apex Data Loader.
My csv file has a date field with value: 01/23/2011 (MM/DD/YYYY)

Now I started data load operation and date will be inserted as 01/11/2012.

Explanation: As data loader is expecting the date in dd/mm/yyyy format while we provided in 01/23/2011 (MM/DD/YYYY) and  23 cannot be a month value, so 23 considered as 1 year and 11 months.
So the result will be 01/11/2012.

Now here you can see data loader is not going to block any record because of this format so you will have to be very careful with this. It's your responsibility to check the data loader setting and date format in csv file.

Please correct me if anything I am making wrong here. comments are welcome.















6 comments:

  1. So, What would be the best solution while uploading the data which's been imported from an ERP system like Baan,
    And one more change is , If we make the European date format to true, It will Upload the data by the earlier date.
    The best way is to format the date in the csv file.

    ReplyDelete
  2. Can someone explain why they would implement it like this, surely the best thing to do would be treat it as an error and move to the next record in the CSV

    ReplyDelete
  3. Finding the time and actual effort to create a superb article like this is great thing. I’ll learn many new stuff right here! Good luck for the next post buddy..
    Fresher Jobs
    Fresher Opening

    ReplyDelete
  4. Hi, Thanks for sharing this post. Excellent explanation of Date format while uploading data in salesforce.

    Salesforce Integration Services

    ReplyDelete