Gary
Luke --------------------------------------------------------------------------------
Big City Production Services P/L Unit E, 13-21 Mandible St., Alexandria NSW
2015 Phone: (02) 9318-2354 Fax: (02) 9319-3929 Email: gary@bigcity.net.au
Mobile: 0439-451-571 www.bigcity.net.au
"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:M7OdnYRLreEULVfanZ2dnUVZ_qTinZ2d@comcast.com. ..
> Gary wrote:
>> I'm successfully importing tables and views from MySQL into Excel via
>> ODBC, except dates before 1900 and incomplete dates of any period. It's
>> historical data about the convicts with dates ranging from the 1700s to
>> the mid 1900s.
>>
>> Excel's query window shows the dates in the right format - eg.
>> 1832-11-19, or 1917-00-00, or blank for Null, but the cells in Excel
>> don't accept them. Nulls appear as empty cells - that's ok. Any date with
>> a "00" for month or day also appear as a blank cell - that's not ok.
>> Complete dates before 1900 appear as "0/01/1900" - that's not ok.
>> Complete dates after 1900 appear in their correct date format as set by
>> Excel.
>>
>> I know that Excel can't handle dates before 1900 or incomplete dates.
>> Does anyone know a work-around so the pre-1900 and incomplete dates can
>> appear as text strings in the format "yyyy-mm-dd".
>>
>> One workaround I found is to copy a table or results of a query or view
>> in Navicat and paste into Excel. That's not completely satisfactory. I
>> have the same problem with ODBC and Access where I want the latest
>> versions of the data without having to know what's been changed in MySQL.
>> Using Navicat's expert to Excel has the same problem.
>>
>> MySQL vs. 5.0.20-nt
>> ODBC vs. 3-51-14
>> Excel vs.2002
>>
>>
>> Gary Luke
>> Sydney, Australia
>
> And your MySQL question is?


This was in my message.

"Does anyone know a work-around so the pre-1900 and incomplete dates can
appear as text strings in the format "yyyy-mm-dd"."