Bug #73541 Export Data - Date type not auto-detected when locale is different
Submitted: 11 Aug 2014 23:21 Modified: 9 Oct 2014 23:27
Reporter: Javier Treviño Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S3 (Non-critical)
Version:1.2.0 OS:Any
Assigned to: CPU Architecture:Any

[11 Aug 2014 23:21] Javier Treviño 
Description:
The data type of columns containing only dates is not auto-detected correctly by the Export Data operation when the locale is such that the date format is different than MM/DD/YYYY.

How to repeat:
1. Make sure the computer's date format is different than MM/DD/YYYY (e.g. change the locale to Spanish (Spain).
2. Open MySQL for Excel
3. Setup some data in a worksheet containing dates with a format different than MM/DD/YYYY (e.g. DD/MM/YYYY)
4. Select the data of step 3, and in any schema, click on Export Data to New Table
5. On the preview grid, click on the column containing the dates and note the data type was detected as VarChar instead of Date or DateTime.
[25 Aug 2014 21:05] Javier Treviño 
Posted by developer:
 
Changed the way null and zero dates are handled, in previous versions MySQL zero dates ("0000-00-00 00:00:00") were imported in Excel as the minimum valid date allowed by .NET (DateTime.MinValue). Excel ListObjects bound to DataTables containing DateTime.MinValue are automatically converted into a text representation so the cell's value is no longer recognized as a date (possible VSTO bug). Now zero dates are always treated as null, so even typing a 0 in a date column translates to a null date.

Logic that recognizes date values string or boxed objects was rewritten from scratch to fix also bugs with dates in a locale different than US English.
[9 Oct 2014 23:27] Philip Olson 
Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.3 release, and here's the changelog entry:

Changed the way NULL and zero dates are handled. Previously, MySQL zero
dates ("0000-00-00 00:00:00") were imported into Excel as the minimum
valid date allowed by .NET (DateTime.MinValue), which was then converted
into a text representation where the cell's value was no longer recognized
as a date. Now, zero dates are always treated as NULL.

Thank you for the bug report.