+ Start a Discussion
yoginimane89yoginimane89 

Import::Date field issue

Hi,

 

I am importing one file that files contains one date field to import. After importing that file date field is not importing as it is in csv file, instead it is importing date by less one day to mentioned date ex.- my start date in 9/6/2012 after import it shows 9/5/2012 as start date. I have no formula or any validation rule on that field also my Time zone and locale setting are as per needed.

Can anyone tell what is the issue and how to correct it? 

 

Thanks.

Best Answer chosen by Admin (Salesforce Developers) 
BewitchedBewitched

Check out this knowledge article

 

Data Loader Import data for Date or Date Time field

Knowledge Article Number: 000004680


Description
When admin/user import date or date time field using Data Loader, admin/user always notice the Data uploaded from CSV files doesn't match with what are displaying from Salesforce records when admin/user check those imported records after login to admin/user SF account using web browser.

 

Resolution
To insert record into Date or Date Time field using Data Loader:

1) "Date" type field accept either one of below format:
yyyy-mm-dd
yyyy-mm-dd hh:mm:ss
yyyy-mm-ddThh:mm:ssZ
yyyy-mm-ddThh:mm:ss.sssZ


2) "Date Time" type field accept either one of below format:
yyyy-mm-dd hh:mm:ss
yyyy-mm-ddThh:mm:ssZ
yyyy-mm-ddThh:mm:ss.sssZ


Example given below is based on user/admin set their SF timezone to "China/Taiwan timezone GMT+8" (admin/user set his or her time zone in SF under Setup | Personal Setup | My Personal Information | Personal Information | Timezone Setting )


If your field Data Type is "Date" type and Data Loader Timezone setting: "GMT+0" or "GMT" or blank, your example data in your import spreadsheet can be either one of below:
2011-01-10
2011-01-10 00:00:00
2011-01-10T00:00:00Z
2011-01-10T00:00:00.000Z


If your field Data Type is "Date" type and Data Loader Timezone setting: "GMT+8", your example data in your import spreadsheet must only be either one of below (if you set the hh:mm:ss to 00:00:00, the import date data in SF will display previous day '2011-01-09' instead of '2011-01-10'):
2011-01-10 08:00:00
2011-01-10T08:00:00Z
2011-01-10T08:00:00.000Z


If your field Data Type is "Date Time" type and Data Loader Timezone setting: "GMT+0" or "GMT" or blank, your data display in SF will be 8 hours ahead of your time XX:XX:XX (Basically it messes up all your date time data)
2011-01-10 XX:XX:XX+08:00 (This No Longer supported in Data Loader 20)
2011-01-10TXX:XX:XX+08:00 (This No Longer supported in Data Loader 20) 2011-01-10TXX:XX:XX.XXX+08:00 (This No Longer supported in Data Loader 20)


If your field Data Type is "Date Time" type and Data Loader Timezone setting: "GMT+8", your example data in your import spreadsheet must be either one of below in order to have the Date Time displaying from SF exactly what you imported in spreadsheet:
2011-01-10 XX:XX:XX
2011-01-10TXX:XX:XXZ
2011-01-10TXX:XX:XX.XXXZ

 

Note:
1) XX:XX:XX or XX:XX:XX.XXX symbol stands for the variable time you key in.
2) Above information is only applicable for SF latest Data Loader (is Data Loader version 20)

All Answers

BewitchedBewitched

Hi,

Which tool are you using for import?

Thanks,

yoginimane89yoginimane89

I am using Data Loader for importing.

BewitchedBewitched

Check out this knowledge article

 

Data Loader Import data for Date or Date Time field

Knowledge Article Number: 000004680


Description
When admin/user import date or date time field using Data Loader, admin/user always notice the Data uploaded from CSV files doesn't match with what are displaying from Salesforce records when admin/user check those imported records after login to admin/user SF account using web browser.

 

Resolution
To insert record into Date or Date Time field using Data Loader:

1) "Date" type field accept either one of below format:
yyyy-mm-dd
yyyy-mm-dd hh:mm:ss
yyyy-mm-ddThh:mm:ssZ
yyyy-mm-ddThh:mm:ss.sssZ


2) "Date Time" type field accept either one of below format:
yyyy-mm-dd hh:mm:ss
yyyy-mm-ddThh:mm:ssZ
yyyy-mm-ddThh:mm:ss.sssZ


Example given below is based on user/admin set their SF timezone to "China/Taiwan timezone GMT+8" (admin/user set his or her time zone in SF under Setup | Personal Setup | My Personal Information | Personal Information | Timezone Setting )


If your field Data Type is "Date" type and Data Loader Timezone setting: "GMT+0" or "GMT" or blank, your example data in your import spreadsheet can be either one of below:
2011-01-10
2011-01-10 00:00:00
2011-01-10T00:00:00Z
2011-01-10T00:00:00.000Z


If your field Data Type is "Date" type and Data Loader Timezone setting: "GMT+8", your example data in your import spreadsheet must only be either one of below (if you set the hh:mm:ss to 00:00:00, the import date data in SF will display previous day '2011-01-09' instead of '2011-01-10'):
2011-01-10 08:00:00
2011-01-10T08:00:00Z
2011-01-10T08:00:00.000Z


If your field Data Type is "Date Time" type and Data Loader Timezone setting: "GMT+0" or "GMT" or blank, your data display in SF will be 8 hours ahead of your time XX:XX:XX (Basically it messes up all your date time data)
2011-01-10 XX:XX:XX+08:00 (This No Longer supported in Data Loader 20)
2011-01-10TXX:XX:XX+08:00 (This No Longer supported in Data Loader 20) 2011-01-10TXX:XX:XX.XXX+08:00 (This No Longer supported in Data Loader 20)


If your field Data Type is "Date Time" type and Data Loader Timezone setting: "GMT+8", your example data in your import spreadsheet must be either one of below in order to have the Date Time displaying from SF exactly what you imported in spreadsheet:
2011-01-10 XX:XX:XX
2011-01-10TXX:XX:XXZ
2011-01-10TXX:XX:XX.XXXZ

 

Note:
1) XX:XX:XX or XX:XX:XX.XXX symbol stands for the variable time you key in.
2) Above information is only applicable for SF latest Data Loader (is Data Loader version 20)

This was selected as the best answer
BewitchedBewitched

Also check the timezone of Data loaderr!

Go into the settings of Apex Loader and change the timezone to GMT

yoginimane89yoginimane89

I have set the Time Zone for Data loader as GMT and imported data. Now it is as per mentioned in csv file.

 

Thanks. 

ethanoneethanone

blank or null values are causing my api inserts to fail. how do I format an null value for a date field?