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
> "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
>
>
>

DATE_FORMAT will return the date as a string if it is used in a string
context. I haven't tried this particular thing, but maybe something
like CONCAT(DATE_FORMAT(...), '') will do it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================