function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Ian Bate 9Ian Bate 9 

Data Loader Date Issue - Not the usual one

Hi there

I have an Amazon VM Windows server and I have a Powershell script to download a CSV file. The date format in the CSV file is DD/MM/YYYY. I can't change the date format in the file becaus this is intended to be a daily automated process. Lots of people on the forum have problems with dates going in one day offset etc. but this problem is something else...

The Powershell script runs Data Loader on the command line to load the CSV file. Data Loader settings have the 'Use European Date Format' checked.

The server Control Panel has all the regional settings, time zone etc. set to UK.

I'm loading the dates in the CSV file into SF Date type field.

Problem is that the dates get transposed, e.g. June 1st 2018 in the file is 01/06/2018 but ends up in SF as Jan 6th.

Does anyone have any ideas? My workaround will be to load it into a text field and then convert/transpose it but I'd rather not. And I shouldn't have to should I?

Thanks in advance.

 
Best Answer chosen by Ian Bate 9
Alain CabonAlain Cabon
Hi,

There is an option : set the following in the process-config.xml file.

<entry key="process.useEuropeanDates" value="true"/>
  • process.useEuropeanDates  = boolean = Use European date formatSelect this option to support the date formats dd/MM/yyyy and dd/MM/yyyy HH:mm:ss.  Sample value: true
https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/loader_params.htm

A sample process-conf.xml file is in the \samples\conf directory that’s installed with Data Loader.

Data Types Supported by Data Loader​:
https://help.salesforce.com/articleView?id=supported_data_types.htm&type=5&lang=en

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/yyyy
  • yyyyMMdd
Note the following tips for date formats:
  • To enable date formats that begin with the day rather than the month, select the Use European date formatbox in the Settings dialog. European date formats are dd/MM/yyyy and dd/MM/yyyy HH:mm:ss.
  • If your computer's locale is east of Greenwich Mean Time (GMT), we recommend that you change your computer setting to GMT in order to avoid date adjustments when inserting or updating records.

If you read the source code of the dataloader : 

1) com.salesforce.dataloader.config.Config.java:

                    public static final String EURO_DATES = "process.useEuropeanDates"; //$NON-NLS-1$

2) com.salesforce.dataloader.dyna.SforceDynaBean
/**
     * Register dynabean data type converters for common java data types
     * @param useEuroDates if true, european date format will be used
     */
    synchronized static public void registerConverters(Config cfg) {
        final boolean useEuroDates = cfg.getBoolean(Config.EURO_DATES);
        final TimeZone tz = cfg.getTimeZone();
        // Register DynaBean type conversions
        ConvertUtils.register(new DateConverter(tz, useEuroDates), Calendar.class);
       ...
    }


3) DateConverter.java (https://github.com/forcedotcom/dataloader/blob/master/src/main/java/com/salesforce/dataloader/dyna/DateConverter.java)
https://github.com/forcedotcom/dataloader/blob/master/src/main/java/com/salesforce/dataloader/dyna/DateConverter.java
 
/* Helper function to produce all the patterns that DL supports */
    private static List<String> getSupportedPatterns(boolean europeanDates) {

       ...
        String baseDate = europeanDates ? "dd/MM/yyyy" : "MM/dd/yyyy";

       ...
        return basePatterns;
    }
}

String baseDate = europeanDates ? "dd/MM/yyyy" : "MM/dd/yyyy";

 

All Answers

Alain CabonAlain Cabon
Hi,

There is an option : set the following in the process-config.xml file.

<entry key="process.useEuropeanDates" value="true"/>
  • process.useEuropeanDates  = boolean = Use European date formatSelect this option to support the date formats dd/MM/yyyy and dd/MM/yyyy HH:mm:ss.  Sample value: true
https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/loader_params.htm

A sample process-conf.xml file is in the \samples\conf directory that’s installed with Data Loader.

Data Types Supported by Data Loader​:
https://help.salesforce.com/articleView?id=supported_data_types.htm&type=5&lang=en

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/yyyy
  • yyyyMMdd
Note the following tips for date formats:
  • To enable date formats that begin with the day rather than the month, select the Use European date formatbox in the Settings dialog. European date formats are dd/MM/yyyy and dd/MM/yyyy HH:mm:ss.
  • If your computer's locale is east of Greenwich Mean Time (GMT), we recommend that you change your computer setting to GMT in order to avoid date adjustments when inserting or updating records.

If you read the source code of the dataloader : 

1) com.salesforce.dataloader.config.Config.java:

                    public static final String EURO_DATES = "process.useEuropeanDates"; //$NON-NLS-1$

2) com.salesforce.dataloader.dyna.SforceDynaBean
/**
     * Register dynabean data type converters for common java data types
     * @param useEuroDates if true, european date format will be used
     */
    synchronized static public void registerConverters(Config cfg) {
        final boolean useEuroDates = cfg.getBoolean(Config.EURO_DATES);
        final TimeZone tz = cfg.getTimeZone();
        // Register DynaBean type conversions
        ConvertUtils.register(new DateConverter(tz, useEuroDates), Calendar.class);
       ...
    }


3) DateConverter.java (https://github.com/forcedotcom/dataloader/blob/master/src/main/java/com/salesforce/dataloader/dyna/DateConverter.java)
https://github.com/forcedotcom/dataloader/blob/master/src/main/java/com/salesforce/dataloader/dyna/DateConverter.java
 
/* Helper function to produce all the patterns that DL supports */
    private static List<String> getSupportedPatterns(boolean europeanDates) {

       ...
        String baseDate = europeanDates ? "dd/MM/yyyy" : "MM/dd/yyyy";

       ...
        return basePatterns;
    }
}

String baseDate = europeanDates ? "dd/MM/yyyy" : "MM/dd/yyyy";

 
This was selected as the best answer
Ian Bate 9Ian Bate 9
Thanks Alain

OK, so what I now understand is that when you run Data Loader at the command line it ignores the Settings that I set in the DL UI. So I added <entry key="process.useEuropeanDates" value="true"/> to the config and that sorted the issue.

However, I still have the problem with dates being loaded as the previous day... So, my solution is to load the date as a Text field and added a formula DATE(VALUE(MID(TXT__c, 7, 4)),VALUE(MID(TXT__c, 4, 2)),VALUE(MID(TXT__c, 1, 2))).
Alain CabonAlain Cabon
Hi Ian,

The previous day is a problem of time zone used by the Microsoft Windows user that you use for the loadings with the data loader.

1) Note the following tips for date formats:​
  • If your computer's locale is east of Greenwich Mean Time (GMT), we recommend that you change your computer setting to GMT in order to avoid date adjustments when inserting or updating records.
https://help.salesforce.com/articleView?id=supported_data_types.htm&type=5&lang=en

Computer's locale is not the sames as the Salesforce locale of the connected user.

2) Time Zone: Select this option to specify a default time zone.

If a date value does not include a time zone, this value is used.
  • If no value is specified, the time zone of the computer where Data Loader is installed is used.  (your case is here)
  • If an incorrect value is entered, GMT is used as the time zone and this fact is noted in the Data Loader log.
  • Valid values are any time zone identifier which can be passed to the JavagetTimeZone(java.lang.String) method. The value can be a full name such as America/Los_Angeles, or a custom ID such as GMT-8:00
https://help.salesforce.com/articleView?id=configuring_the_data_loader.htm&type=5

3) How to Change the Time Zone in Windows 10​: should be GMT.
https://www.tenforums.com/tutorials/6401-change-time-zone-windows-10-a.html