On 09-May-01 Darla Baker wrote:
> I have a text file which is extracted from a non-sql
> database each night and then a cron sql script runs to
> insert the text data into the mysql database tables.
>
> My problem is that the date data in the text file is
> formatted incosistently (12/31/00 or 12-31-00) and so
> the fields that hold date data are currently char
> datatypes.
>
> Since I need the dates to be dates for queries, I need
> a solution which will allow me to insert the date data
> into the database as a date field datatype.
<snip>
Create a holding table to load:
create table bar (
txtdate char(10) not null default '00-00-00',
foo char(30) default 'da_foo'
);
Load the data:
mysql> select * from bar;
+----------+-------+
| txtdate | foo |
+----------+-------+
| 12/31/00 | foo 1 |
| 05-09-01 | foo 2 |
+----------+-------+
Add the DATE column:
mysql> alter table bar add column da_date date not null default '1900-02-31'
-> after txtdate;
Do the conversion:
mysql> update bar set da_date=
-> concat(concat(right(txtdate,2),'-'),left(txtdate,5));
Cleanup:
mysql> alter table bar drop column txtdate;
mysql> select * from bar;
+------------+-------+
| da_date | foo |
+------------+-------+
| 2000-12-31 | foo 1 |
| 2001-05-09 | foo 2 |
+------------+-------+
Insert into your main working table:
insert into blah (my_date,my_foo)
select * from bar;
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
