469,282 Members | 2,078 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

How to specify Date format during LOAD DATA INFILE ?

Will it be possible to specify the date format of type DATE during data
loading ?
Jul 23 '05 #1
8 10862
Ray in HK wrote:
Will it be possible to specify the date format of type DATE during data
loading ?

I don't think it is possible. Why not import the data (maybe with the
date column in some new dummy column) and then reformat the data in the
correct representation using a query on the table?

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?
Jul 23 '05 #2
Ray in HK wrote:
Will it be possible to specify the date format of type DATE during data
loading ?


"Although MySQL tries to interpret values in several formats, dates
always must be given in year-month-day order (for example, '98-09-04'),
rather than in the month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98', '04-09-98')."

http://dev.mysql.com/doc/mysql/en/da...ime-types.html

Regards,
Bill K.
Jul 23 '05 #3
Thanks, I did read it but just ask if there are any tricks can be played.

"Bill Karwin" <bi**@karwin.com> 在郵件 news:d9*********@enews1.newsguy.com
中撰寫...
Ray in HK wrote:
Will it be possible to specify the date format of type DATE during data
loading ?


"Although MySQL tries to interpret values in several formats, dates
always must be given in year-month-day order (for example, '98-09-04'),
rather than in the month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98', '04-09-98')."

http://dev.mysql.com/doc/mysql/en/da...ime-types.html

Regards,
Bill K.

Jul 23 '05 #4
Well , it's because there are quite lots of data and for testing purposes, I
need to re-load the data again and again while the dev machine is
s...l..o...w.
Anyway, thx for your advice. It'd be better if Mysql has such feature as
Oracle that allow the format to be specified during batch data loading.

"Jonathan" <jo******@heelal.nl> 在郵件
news:42**********************@text.nova.planet.nl 中撰寫...
Ray in HK wrote:
Will it be possible to specify the date format of type DATE during data
loading ?

I don't think it is possible. Why not import the data (maybe with the
date column in some new dummy column) and then reformat the data in the
correct representation using a query on the table?

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

Jul 23 '05 #5
> "Jonathan" <jo******@heelal.nl> 在郵件
news:42**********************@text.nova.planet.nl 中撰寫...
Ray in HK wrote:
Will it be possible to specify the date format of type DATE during data
loading ?
I don't think it is possible. Why not import the data (maybe with the
date column in some new dummy column) and then reformat the data in the
correct representation using a query on the table?

Jonathan


Ray in HK wrote: Well , it's because there are quite lots of data and for testing purposes, I need to re-load the data again and again while the dev machine is
s...l..o...w.
Anyway, thx for your advice. It'd be better if Mysql has such feature as
Oracle that allow the format to be specified during batch data loading.


I still think as it is for testing that you could once do the import and
then afterwards do a conversion as I suggested.

After you have done this backup the mysql table with the mysqldump tool
or the select into method. This way it is possible for you to restore
earlier status just by restoring your tables from the mysql backup/dump
and you only need to do the conversion of the date once.

Normally importing from a text file is slower than inserting from a dump
file as you can use some tricks which are explained here:

Explanation about the mysqldump utility:
http://dev.mysql.com/doc/mysql/en/mysqldump.html

Information about improving the speed of insert statements:
http://dev.mysql.com/doc/mysql/en/insert-speed.html

I cannot imagine that for testing all data need to be reloaded and
converted all the way from the source again as I think you use the same
set for testing over and over again.

Jonathan

N.B. One word of advice for posting in newsgroups/usenet:
I rearanged our conversation a little bit. For the future please don't
top post as this makes reading conversation more difficult. The most
recent answers in newsgroups are normally posted at the bottom and
redundant information is removed. If you want to answer multiple
questions asked in one message just post your answers in between.

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?
Jul 23 '05 #6
[snip]

N.B. One word of advice for posting in newsgroups/usenet:
I rearanged our conversation a little bit. For the future please don't
top post as this makes reading conversation more difficult. The most
recent answers in newsgroups are normally posted at the bottom and
redundant information is removed. If you want to answer multiple
questions asked in one message just post your answers in between.

My congratulations on the most sensible, user-friendly advice I've ever read
about top-posting. Too often, all one reads is a snarl "Don't top-post"
with the chastiser then going back to something equally effective and
significant, like - tearing up yesterday's newspaper, opening and closing
the refrigerator door 1,234 times on the night of a crescent moon, and
sticking pins in a wax image of next door's ginger tom. I thought your
approach exemplary. May your tribe increase!
Jul 23 '05 #7
>
I still think as it is for testing that you could once do the import and
then afterwards do a conversion as I suggested.

After you have done this backup the mysql table with the mysqldump tool
or the select into method. This way it is possible for you to restore
earlier status just by restoring your tables from the mysql backup/dump
and you only need to do the conversion of the date once.


then the testing will be incomplete.
Jul 23 '05 #8
Ray in HK wrote:
I still think as it is for testing that you could once do the import and
then afterwards do a conversion as I suggested.

After you have done this backup the mysql table with the mysqldump tool
or the select into method. This way it is possible for you to restore
earlier status just by restoring your tables from the mysql backup/dump
and you only need to do the conversion of the date once.

then the testing will be incomplete.

Do you really need to test the whole chain over and over again? I think
testing everything modular would improve your development process and
after you've succesfull tested all steps in the sequence you can do some
over all tests of the whole chain.

Or is our goal to port the data to a new database platform regularly?

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?
Jul 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Bruce A. Julseth | last post: by
3 posts views Thread by Otto | last post: by
reply views Thread by Donald Tyler | last post: by
reply views Thread by Karam Chand | last post: by
1 post views Thread by Jami Bradley | last post: by
9 posts views Thread by Shapper | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.