mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.20    |
+-----------+
1 row in set (0.00 sec)

mysql> create database cosmicperl;
Query OK, 1 row affected (0.00 sec)

mysql> use cosmicperl;
Database changed
mysql> create table cosmicperl (id integer auto_increment primary key,
+ dt date not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into cosmicperl values (null, now()),(null, now()-interv
+al 1 day),(null, now()-interval 7 day);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table cosmicperl add column dt_int integer unsigned;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update cosmicperl set dt_int=DATE_FORMAT(dt,'%Y%m%d');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from cosmicperl;
+----+------------+----------+
| id | dt         | dt_int   |
+----+------------+----------+
|  1 | 2007-11-19 | 20071119 |
|  2 | 2007-11-18 | 20071118 |
|  3 | 2007-11-12 | 20071112 |
+----+------------+----------+
3 rows in set (0.00 sec)

mysql>
[download]

Second approach (varchar -> int):

mysql> -- Alternative approach when original value is a string (varcha
+r)
mysql> create database cosmicperl;
Query OK, 1 row affected (0.00 sec)

mysql> use cosmicperl;
Database changed
mysql> create table cosmicperl (id integer auto_increment primary key,
+ dt_str varchar(32) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into cosmicperl values (null, '11:11:2007'), (null, '12:
+11:2007'), (null, '13:11:2007');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table cosmicperl add column dt_int integer unsigned;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update cosmicperl set dt_int=date_format(str_to_date(dt_str, '%
+d:%m:%Y'),'%Y%m%d');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from cosmicperl;
+----+------------+----------+
| id | dt_str     | dt_int   |
+----+------------+----------+
|  1 | 11:11:2007 | 20071111 |
|  2 | 12:11:2007 | 20071112 |
|  3 | 13:11:2007 | 20071113 |
+----+------------+----------+
3 rows in set (0.00 sec)
[download]

Update: Added second approach as pr CountZero's suggestion. I would strongly recommend to use date/timestamp datatypes when storing date and time values in order to benefit from consistency checks and built-in date arithmetic.