th*****************@yahoo.com wrote:
So, I am trying to do a cast/convert. The int is stored as 20040520
(which means 5/20/2004)
What is the most efficient way to do this? Is there a way to cast it
without doing some crazy string manipulation?
Maybe there's a clever, efficient way of doing it, but you don't need
the absolute best way, you just need to get it done, so you can
subsequently use a real DATE field.
For instance, I'd probably do something like this:
ALTER TABLE `table` CHANGE COLUMN DateSold OldDateSold INT;
ALTER TABLE `table` ADD COLUMN DateSold DATE;
UPDATE `table` SET DateSold =
DATE(CONCAT(
OldDateSold DIV 10000, '-',
(OldDateSold DIV 100) % 100, '-',
OldDateSold % 100)
);
....verify everything happened as you intended it to...
ALTER TABLE `table` DROP COLUMN OldDateSold;
Try this on a copy of your database first to make sure it works! :)
Regards,
Bill K.