Exported custom date fields formatted incorrectly

Question

The error seems to occur during the export process and affects only custom date fields, not those that are built-in.

My custom date field is set to use "mm/dd/yyyy", and stores correctly in the sql table as "yyyy-mm-dd hh:mm:ss". But then after export, the CSV in notepad shows "Month day, year" (ex: August 17th, 2015).

The "th" in "17th" prevents Excel from recognizing it as a date when opening the CSV. That can be fixed with find/replace, but doesn't address the root problem.

Where can I change the date export format for custom fields, or at least get rid of those ordinal suffixes?

Answer 1

Based on your description, I think the export format might be controlled by the "Complete Date and Time" and "Complete Date" settings at Administer > Localization > Date Formats. The default values for those fields are:

Complete Date and Time - %B %E%f, %Y %l:%M %P
Complete Date - %B %E%f, %Y

The help text on that screen reads:

Use this screen to configure formats for date display and date input fields. Defaults are provided for standard United States formats. Settings use standard POSIX specifiers. Available specifiers and the values produced will vary based on your operating system and locale.

Commonly Used Specifiers

%b - abbreviated month name ('Jan'..'Dec')
%B - full month name ('January'..'December')
%d - day of the month as a decimal number, 0-padded ('01'..'31')
%e - day of the month as a decimal number, blank-padded (' 1'..'31')
%E - day of the month as a decimal number ('1'..'31')
%f - English ordinal suffix for the day of the month ('st', 'nd', 'rd', 'th')
%H - hour in 24-hour format, 0-padded ('00'..'23')
%I - hour in 12-hour format, 0-padded ('01'..'12')
%k - hour in 24-hour format, blank-padded (' 0'..'23')
%l - hour in 12-hour format, blank-padded (' 1'..'12')
%m - month as a decimal number, 0-padded ('01'..'12')
%M - minute, 0-padded ('00'..'60')
%p - lowercase ante/post meridiem ('am', 'pm')
%P - uppercase ante/post meridiem ('AM', 'PM')
%Y - year as a decimal number including the century ('2015')

If you change the values for those settings, perhaps the export format will change?