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