Gary wrote:
> 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"."
>
>
>
>
>
>
OK, then you just need to use DATE_FORMAT() to convert to a string.
Or use something other then Excel.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================