load with date format

[yasaswi]

I have date values in mm/dd/yy format in a text file. How can use the
LOAD command or mysqlimport command and load those date values.
Currently I just load the date values as text into MySQL tables and
manipulate the text in applications. There must be a better way? can I
use DATE_FORMAT or something like that?
Thanks,
Yasaswi

[Bill Karwin]

yasaswi wrote:

I have date values in mm/dd/yy format in a text file. How can use the
LOAD command or mysqlimport command and load those date values.
Currently I just load the date values as text into MySQL tables and
manipulate the text in applications. There must be a better way? can I
use DATE_FORMAT or something like that?

DATE_FORMAT is only for formatting dates on output.

MySQL will not accept mm/dd/yy format for input of DATE data. You must
use YYYY-MM-DD or YY-MM-DD. See
http://dev.mysql.com/doc/mysql/en/DATETIME.html for more information.

If you need to use another format, your only option is what you have
done: use a text datatype instead of a DATE datatype for storage. But
this prevents you from treating the value as a date value, which means
you can't use the builtin functions in MySQL for operating on date values.

Regards,
Bill K.

[Aggro]

Bill Karwin wrote:

If you need to use another format, your only option is what you have
done: use a text datatype instead of a DATE datatype for storage. But
this prevents you from treating the value as a date value, which means
you can't use the builtin functions in MySQL for operating on date values.

But he could write SQL script to convert the text to date from one
column to another column by using string functions. For example cutting
substrings from the source and pasting those in correct order for date.