Data Loader Date and DateTime Best Practices

Data Loader Date and DateTime Best Practices

Little bit about Salesforce and Data loader:

Salesforce is a CRM and deals with lots of data, we use different objects to hold different types of data. Database tables that stores data So while working on data you need to be careful because small amount of change in data can cause large problems.

Data loader get used when we want to migrate large amount of data to salesforce org, when you need to load 50,000 to 5,000,000 records. Data Loader is supported for loads of up to 5 million records. If you need to load more than 5 million records.

All of the information given above is general information about data loader but here we go for our main point for which we have wrote this blog.

Problem with Date and Date Time:

Date and DateTime are the data types that come up with different formats e.g mm/dd/yyyy, dd/mm/yyyy, etc. Sometimes this creates ambiguity for system to understand exact date to pick.

When you change date format from let’s suppose dd/mm/yyyy to mm-dd-yyyy so then data may be come up with issue of converting date to month or vice versa, this happens rarely.

Also while working with time you need to take care of one more crucial thing i.e. Timezone.
For different regions we have different time zones that may again cause difference in data.

Date and Time Formatting :

• When you migrate data to salesforce org please make sure you are using exact date time format as your salesforce org is using.
• Check it in your company settings.
• Also make sure your data loader uses same format as your salesforce org is using.
• To check and change settings in data loader follow the steps –
In data loader navigate to settings and you will find the some options like Use European date format (dd/mm/yyyy).

Timezone handling:

• When you are working on salesforce org that has different timezone than your local timezone, then make the tool you are using uses same timezone that salesforce has.
• When you use data loader tool to migrate data in salesforce by default it will take your local timezone but in salesforce we have another timezone.
• Suppose in salesforce org you have set timezone as PST (GMT-07:00) but when working you are working in IST i.e. your local timezone.
• So what’s now, you have two different time zones so when you push data to salesforce org salesforce converts data from one timezone to another.
You have entered date as 24-09-2018 and pushed data so it will be convert to PST and it will be -13:30 hours so it will be date as 23-09-2018.
• Here we loss the data as it is showing the difference of one day.
• With DateTime it becomes more worse, as you got to know when we migrate data using one timezone to another timezone conversion takes place.
• Consider a scenario where you have all your data of events that are schedule for next some days and this data is present in excel sheet.
• Now you want to create event or task from that data in salesforce org.
• When you will load data to salesforce org, the date and time will be convert from local time zone i.e. IST to salesforce timezone (PST).
• If you have intended to create event of 2 PM on 14-09-2018 then it will be create event with 1:30 AM 14-09-2018 which is huge difference.
• So due to this you may be loose your data and create problem.

Prevent this problem while migrating data:

• When you migrate data of data type datetime make sure your data loader is set to same timezone as your salesforce org has.
• By default data loader is set to your local timezone.
• To check or change timezone do following steps: In data loader navigate to settings by clicking settings menu from menu bar.
• Here you can select target timezone specify the same time zone as in salesforce org.

Conclusion:

While dealing with data migration of date and date time there can be ambiguity of various things like date and datetime format, timezone. But we can prevent this with some settings and proper formatting of date and date time.