CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Dates do not show in the correct format in csv
Pages: [1]

Author Topic: Dates do not show in the correct format in csv  (Read 3383 times)

LindseyM

  • I post frequently
  • ***
  • Posts: 229
  • Karma: 8
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: 5.5
  • PHP version: 5.3.6
Dates do not show in the correct format in csv
October 06, 2013, 02:06:51 am
We're using 4.3.7 and are storing dates. I have put them in as dd/mm/yyyy and in some places as dd-mm-yyyy but when we export to csv it shows the date in the format October 1st, 2013. It's the same in the 4.3 demo.

This is hard to convert into a date format without some fancy formulas and means us doing another step each time we export. If it formatted it dd/mm/yyyy or anything vaguely similar, even as a text field rather than date field, it'd be much more straightforward.

How can we get the date to show in csv as it's formatted in the custom field?

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Dates do not show in the correct format in csv
October 06, 2013, 02:30:11 am
Lindsey - I think you might be seeing the date as 'displayed' by Excel based on Excel's configured rules for date display (if you're viewing the export in Excel). I just did "Export Primary Fields" from a simple contact search on the 4.3 demo. When I view it in Excel, the birth dates look like this:

5/1/63

... but when I look at the .csv file in a text edit, the actual date format is this (which I think is our standard storage format for dates in the database):

"1963-05-01"
Protect your investment in CiviCRM by  becoming a Member!

LindseyM

  • I post frequently
  • ***
  • Posts: 229
  • Karma: 8
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: 5.5
  • PHP version: 5.3.6
Re: Dates do not show in the correct format in csv
October 06, 2013, 03:18:11 am
Thanks for the speedy reply, Dave. I'll pass this on to the colleague who was stumped by it!

LindseyM

  • I post frequently
  • ***
  • Posts: 229
  • Karma: 8
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: 5.5
  • PHP version: 5.3.6
Re: Dates do not show in the correct format in csv
October 07, 2013, 12:42:11 am
My colleague tried this and said:

If I repeat what Dave did, yes, the birth dates come out as a date format on the csv/Excel versionI download. However, that is not true for the other dated custom fields we created. They come out as text.  It is not an issue with Excel displaying dates - the fundamental format of the data is as text and not as a date.

I need to check if our dated custom fields are set up in a different way to the Birth date one, but I don't know where to find the Birth date set up as it's not a custom field. Please can you point me to it. I've attached a screenshot of one of the custom fields where the date doesn't display properly in the export in case you can see the issue from that.



kennedy

  • I post occasionally
  • **
  • Posts: 119
  • Karma: 5
  • CiviCRM version: 4.5.5
  • CMS version: Drupal
  • PHP version: 5.3.10
Re: Dates do not show in the correct format in csv
October 07, 2013, 03:26:51 am
check your CiviCRM date format on this link: civicrm/admin/setting/date?reset=1
you can also go to  "Advanced Date Input Settings" from this same link and see if the date format matches that of your custom field.

Hope this helps.

jcasharpe

  • I post occasionally
  • **
  • Posts: 57
  • Karma: 5
    • Woodlands Church
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: MariaDB 10.0.13
  • PHP version: 5.5
Re: Dates do not show in the correct format in csv
March 14, 2014, 10:52:53 am
I've just been looking into this for LindseyM and the problem is that in CRM/Export/BAO/Export.php custom fields are rendered by calling CRM_Core_BAO_CustomField::getDisplayValue, which in turn calls CRM_Utils_Date::customFormat but with only one parameter so the date format is not configurable for a Custom Field with type date.

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Dates do not show in the correct format in csv
March 21, 2014, 08:56:51 am
Have you come up with a patch to make it configurable / respect the existing configuration for 'custom' date class? If so, this could potentially be added to core for 4.5 (PR against master on GitHub would be best).
Protect your investment in CiviCRM by  becoming a Member!

jkirsner

  • I post occasionally
  • **
  • Posts: 40
  • Karma: 1
  • CiviCRM version: 4.1.2
  • CMS version: Drupal
  • MySQL version: unsure
  • PHP version: unsure
Re: Dates do not show in the correct format in csv
April 09, 2014, 07:26:18 am
Any update on this?

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Dates do not show in the correct format in csv
April 09, 2014, 10:07:47 am
Haven't heard anything :-(
Protect your investment in CiviCRM by  becoming a Member!

jcasharpe

  • I post occasionally
  • **
  • Posts: 57
  • Karma: 5
    • Woodlands Church
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: MariaDB 10.0.13
  • PHP version: 5.5
Re: Dates do not show in the correct format in csv
April 10, 2014, 01:06:26 pm
I put in a fixed string into Export.php that matches what we need. IIRC there are a number of custom date formats configurable and it wasn't clear which made sense for the custom fields; ideally each custom field would have its own custom date format.

Jon-man

  • I’m new here
  • *
  • Posts: 19
  • Karma: 1
  • Supporting voluntary action since 1975
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7.28
  • MySQL version: 5.1.68-cll
  • PHP version: 5.3.18
Re: Dates do not show in the correct format in csv
May 08, 2014, 03:36:10 am
We are experiencing the same issue. For custom data date fields, even though the format is configured  one way (e.g. dd/mm/yyyy), the csv export for the date field is coming out as [month name] [day], [year] (e.g. January 1st, 2014).

I think I agree with jcasharpe, each custom date field should have its own export format.
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Dates do not show in the correct format in csv

This forum was archived on 2017-11-26.