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:IbidnYTcw6FMVlfanZ2dnUVZ_h3inZ2d@comcast.com. ..
> 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.
It doesn't work. It reformats how the date appears to the user, but doesn't
convert it to string data. See my other reply for details.
Thanks though for pitching in with ideas.
Gary
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================
>