I get a data feed over which I have no influence WRT format.

It comes with dates specified as mm/dd/yyyy.

I'd wanted to use something like the following statement to load the
data, but the date format is a problem.

LOAD DATA INFILE 'C:/data/myfeedfile.txt' INTO TABLE mytable;

I get an error message like "Incorrect date value: '11/22/1999' for
column 'start_date' at row 1"

Can I tell MySQL to interpret the date in the format provided as a date
and be confident that it will do so properly? If so, how? In other
words, if mm/dd/yyyy is not a valid date, as defined by MySQL, is there
a way to convert it into a valid date within MySQL? I do not want to
have to write an additional program to reformat the dates prior to
loading the data into MySQL!

Thanks,

Ted

mysql> select cast('11/20/2006' as DATE) as T_DATE;
+----------------+
| T_DATE |
+----------------+
| 11/20/2006 |
+----------------+

>From the docs:

mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
-> INTO TABLE bit_test (@var1) SET b= CAST(@var1 AS SIGNED);

you could (not tested) maybe use set date_col=cast(@var1 as DATE);

This didn't work for me. I submitted ( using MySQL v 5.0.16 and the
MySQL Query Browser) "select cast('11/20/2006' as DATE) as T_DATE;"
exactly as you show it, and I get an error # 1292, "truncated incorrect
datetime value: '11/20/2006'"

In the resultset, the column name is right, but the value is set as
null.

If I use the second statement you suggest, I suppose I will have to
list all of the columns, won't I. It is a rather large table!

BYW: Using the chm version of the manual, I could not find anything on
"cast". Is that a built in function? If so, where will I find more
information about it? If it is builtin, can I create a UDT which
breaks the date format I am receiving into the day, month and year
components (perhaps using regular expressions?), and then overload cast
to convert that UDT into a proper DATE object? Or does SQL not support
UDTs?

Thanks,

Ted

Ted wrote:
> LOAD DATA INFILE 'C:/data/myfeedfile.txt' INTO TABLE mytable;
>
> I get an error message like "Incorrect date value: '11/22/1999' for
> column 'start_date' at row 1"
>
> Can I tell MySQL to interpret the date in the format provided as a date
> and be confident that it will do so properly? If so, how? In other
> words, if mm/dd/yyyy is not a valid date, as defined by MySQL, is there
> a way to convert it into a valid date within MySQL? I do not want to
> have to write an additional program to reformat the dates prior to
> loading the data into MySQL!

No, there's no way to tell MySQL that mm/dd/yyyy is a valid date format
on input. See http://dev.mysql.com/doc/refman/5.0/en/datetime.html for
valid input formats.

Another solution is to create a temporary table for the LOAD DATA
destination, and make the column for your date value a CHAR instead.
Then copy the data to your real destination table, and use the
STR_TO_DATE() function to interpret the mm/dd/yyyy string as a date.

INSERT INTO real_table (a, b, c, datecol)
SELECT a, b, c, STR_TO_DATE(datecol_as_string, '%m/%d/%Y')
FROM temp_table;

See http://dev.mysql.com/doc/refman/5.0/...functions.html
for docs on the STR_TO_DATE() function.

Regards,
Bill K.

>This didn't work for me. I submitted ( using MySQL v 5.0.16 and the
>MySQL Query Browser) "select cast('11/20/2006' as DATE) as T_DATE;"
>exactly as you show it, and I get an error # 1292, "truncated incorrect
>datetime value: '11/20/2006'"

I don't think a cast will work here, as the date isn't in the correct
format. However, something like:

str_to_date('11/20/2006', '%m/%d/%Y')

should convert the date to a format MySQL likes.

Gordon L. Burditt

Ted wrote:
> BYW: Using the chm version of the manual, I could not find anything on
> "cast". Is that a built in function? If so, where will I find more
> information about it?

For what it's worth, CAST() docs are here:
http://dev.mysql.com/doc/refman/5.0/...functions.html

But I don't think that CAST() will solve this problem.

I suppose it might work to do this:

mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
-> INTO TABLE bit_test (@var1)
-> SET datecol = STR_TO_DATE(@var1, '%m/%d/%Y');

> If it is builtin, can I create a UDT which
> breaks the date format I am receiving into the day, month and year
> components (perhaps using regular expressions?), and then overload cast
> to convert that UDT into a proper DATE object? Or does SQL not support
> UDTs?

No, MySQL does not support UDT's.

Regards,
Bill K.

OK, putting it all together, I tried the following:

LOAD DATA INFILE 'C:/data/fund.txt' INTO TABLE fund
('key',@var1,'fund_name','associated_index','group _name','fund_type_name',

'investment_objective','investment_strategy','deta iled_classification',
'rrsp_min_invest',
'non_rrsp_min_invest','pac_plan','swp_plan','swp_m in_balance','legal_status','trust_type','currency' ,
'how_sold','rrsp_eligible','resp_eligible',
'load','no_load_fund','choice_of_front_or_back_fee ',
'max_front_end_percentage','max_back_end_percentag e','back_end_fee_applied_to','management_fee_perce nt',
'available_in_province')
SET start_date=STR_TO_DATE(@var1,'%m/%d/%y');

MySQL Query Browser displays an error, which I assume is from MySQL v
5.0.19 (a different machine that the other I used), indicatng there is
an error on the second line. It doesn't say what the error is though.
All it gives is a number '1064'

There is no mention of there being a problem with my use of
STR_TO_DATE.

Any idea as to what may be awry?

Ted

>OK, putting it all together, I tried the following:
>
>LOAD DATA INFILE 'C:/data/fund.txt' INTO TABLE fund
>('key',@var1,'fund_name','associated_index','grou p_name','fund_type_name',

Quote field names with backquotes, not single quotes.

Gordon L. Burditt

Still no joy!

The statement now looks like:

LOAD DATA INFILE 'C:/data/fund.txt' INTO TABLE fund
(`key`,@date_as_string,`fund_name`,`associated_ind ex`,`group_name`,`fund_type_name`,

`investment_objective`,`investment_strategy`,`deta iled_classification`,`rrsp_min_invest`,

`non_rrsp_min_invest`,`pac_plan`,`swp_plan`,`swp_m in_balance`,`legal_status`,`trust_type`,`currency` ,

`how_sold`,`rrsp_eligible`,`resp_eligible`,`load`, `no_load_fund`,`choice_of_front_or_back_fee`,

`max_front_end_percentage`,`max_back_end_percentag e`,`back_end_fee_applied_to`,`management_fee_perce nt`,
`available_in_province`)
SET start_date=STR_TO_DATE(@date_as_string,'%m/%d/%y');

This gets to examine the data, so I guess the syntax is OK. But I
still get an error about the date. This time, the error is "Truncated
incorrect date value: "11/22/1999"

Any more ideas?

Is MySQL trying to interpret the date value before the call to
STR_TO_DATE()?

What is likely to happen if I add a column called date_as_string
instead of trying to load it into @var1? Do the names in the list in
the LOAD DATA statement have to be provided in the order the
corresponding columns were created in the table? If not, I can load
the data first, and that with the data going into a column of type
char[10], and then run an UPDATE statement in which the SET start_date
clause above is invoked. Right?

Ted

Ted wrote:
> Is MySQL trying to interpret the date value before the call to
> STR_TO_DATE()?

Yeah, that's what it appears like.

> What is likely to happen if I add a column called date_as_string
> instead of trying to load it into @var1?

That's what I would try next. I haven't had experience doing this
precise solution, but it sounds good.

Regards,
Bill K.