Date Import bug in 4.5.x

[Upperholme]

I'm working with a client who importing membership data into Civi. We're using 4.5.0 currently and will update to 4.5.2 as soon as is practicable.

Their membership data includes some additional data information and so custom fields have been created to handle this. The date format for the custom fields is set to dd/mm/yyyy.

The data format for the entire Civi install is set to dd/mm/yyyy.

The dates in the import data are all in the format dd/mm/yyyy.

During the import we set the date format in the import wizard to be dd/mm/yyyy.

When we import the records they then display incorrectly in CiviCRM.
A date such as 12/07/2014 is imported and displays as 07/12/2014 (December 7th 2014)
A date such as 14/07/2014 is imported and displays as 01/01/1970 (January 1st 1970)

Our tests indicate that for any date where the 'dd' element is 12 or less the date is being transposed (i.e. the 'dd' element is being used for month information and the 'mm' element is being used for day information.

where the 'dd' element is larger than 12 the date is being displayed as 01/01/1970.

I tried to repeat this on the demo site, which is running 4.5.2 and got a different result. In this case we couldn't import the data. CiviCRM decided that the date 14/07/2014 was not valid.

This evidence suggests that despite specifying dd/mm/yyyy as the date format for the import, the import script is ignoring this setting, and instead is parsing the data as if it were mm/dd/yyyy, and consequently generating the error report as the 'mm' element of the date clearly cannot be 14.

Interestingly the other date fields in the membership data that we were importing, such as 'Member Since', Membership Start Date' and 'Membership Expiry Date', all appeared to be imported correctly in our 4.5.0 site, but I didn't test this aspect on the demo site as our import wasn't allowed to proceed due to the error noted above.

So, whether its the import script that is the cause of the issue, or the custom field definition I don't know.

Either way I'd say this is a bug, and I'll log an issue accordingly.

Any insight on this most welcome.

[Ed van Leeuwen]

I have observed the same problem, Graham. At first, I thought this has something to do with the date preference setting for the 'birth' field. So I increased the 'Start Offset' to accompany all possible values (and checked that in the interface). This did not have the proper result.

Then I set all date formats to 'yyyy-mm-dd' and chose the same during the import. This way all date fields are properly imported. Perhaps this is a work-around.

Could you please post the issue number you have created?

[Upperholme]

Hi Ed, and thanks for responding.

Apologies for not posting the issue reference here - it is https://issues.civicrm.org/jira/browse/CRM-15513
Feel free to chip in with your experience on that issue.
As I recall we implemented a similar workaround to the one you describe.

Best wishes
Graham