update `table` set `date`=CONCAT(SUBSTR(`date`,7,4), SUBSTR(`date`,4,2
+), SUBSTR(`date`,1,2));
[download]

MySQL is implied by the title. Moreover, you're assuming that the date field is a textual one, which could not be the case, and the OP is asking for an int field. andreas1234567's approach seems more robust.

Flavio
perl -ple'$_=reverse' <<<ti.xittelop@oivalf

Io ho capito... ma tu che hai detto?

Missed the (obvious) MySQL reference in the title, sorry.
The data in the date field is in the format dd:mm:yyyy, which indicates that it is _not_ a datetime field (so it must be a text field). In this case andreas1234567's approach would not work.

--
Ignorance killed the cat, curiosity was framed.

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):

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.

Nice, but the OP indicated that his "date" field is in the format "dd:mm:yyyy" so it seems not of type date and then your solution will not work.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

If you have good enough access to the databases (directly or through a DBA), it would probably be good to use a real date column. Assuming MySQL, your dates can be converted with something like: update table set realdate = STR_TO_DATE(date, '%d:%m:%Y'). This will make dates like "2007-11-21". You can have you application strip the "-" signs in Perl ($date =~ y/-//d) or SQL (DATE_FORMAT(realdate,'%Y%m%d')). Later, the "realdate" column could replace the old date column.

If you can't create a new column or just don't want it, you could run the conversion internally like: update table set date = DATE_FORMAT(STR_TO_DATE(date,'%d:%m:%Y'),'%Y%m%d'). It seems a bit inefficient but it's easy to write and might be OK if your DB server is fast. The danger of going through the date functions is that the dates have to be valid (eg, not 31:02:2007) so I'd run a select (select to file if possible) before trusting an update.

For speed, leaving off any WHERE statements should help. If you still can't get this done in time, use WHERE to split the updates by year or even month-year with something like where date like '%11:2007'.

If you aren't using MySQL, the internal date format might be different but the SQL commands will probably be the same (adjusting for quoting style).

Another idea:

Dump the database tables to a file, process the file with a script offline, then load the modified data back into the database. This just uses the same commands you would normally use to create/restore a database backup.