Gary schreef:
> 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
> "Luuk" <Luuk@invalid.lan> wrote in message
> news:a8qq95-7t6.ln1@leafnode.a62-251-88-195.adsl.xs4all.nl...
>> Gary schreef:
>>> 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".
>>>
>> because Excel wont recognize dates before 1900 you will have to send TEXTs
>> to Excel
>>
>> so, you are giving the 'workaround' yourself
>>
>> i think you can convert a date to a string in MySQL using the
>> DATE_FORMAT() function.
>
>
> Thanks, but date_format(thedate, '%Y-%m-%d') doesn't work. I tried it in a
> view of a table. The column where that should appear doesn't get read into
> Excel. It reformats the appearance of the date field but doesn't change it
> to string data. Try it with date_format(thedate, '%Y-%a-%d) which reformats
> 1937-10-23 to 1937-Oct-23. Dates in this format sort on the real date, not
> on the character string of the month.
>
> Also, if it did work, it would mean creating a set of views in parallel with
> the existent tables, views and queries in MySQL just for the Excel export.
>
> The function str_to_date() exists, but there doesn't seem to be anything
> like a date_to_str() function.
>
>
> Gary
>
>>
>>
>> --
>> Luuk
>
>
what you need is the correct value coming out of MySQL, and i think you
already have done that.
but the real 'problem' you have it that excel does something when importing
i.e when importing these 6 lines:
1850-00-00
1850-01-01
1900-00-00
1900-01-01
1950-00-00
1950-01-01
then excel will only recognize the 4th en 6th line as a DATE
This is because Excel 'thinks' that lines 1,2,3 and 5 contain no DATE-value!
If you want to have these 6 values in excel you have to read them as TEXT
The copying/pasting stuff you did from Navicat, probably just takes the
TEXT-values to Excel, and does not 'import' them
hm, in fact Jerry is right, this does not seem a MySQL question... ;-)
--
Luuk