convert int date to datetime

[thomasamillergoogle]

Hello,
I have a table that unfortunatley has a field 'DateSold' with datatype
of int that SHOULD be datetime.

So, I am trying to do a cast/convert. The int is stored as 20040520
(which means 5/20/2004)

I tried saying Select col1 from table where cast(DateSold as DATETIME)

'1/1/2004'

but of course it bombs out.

What is the most efficient way to do this? Is there a way to cast it
without doing some crazy string manipulation?

[Bill Karwin]

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.

[thomasamillergoogle]

Unfortunatley I am not the DBA, or I would have done this first thing.
I already suggested changing it from int to datetime and got shot down
quickly.

So, is there a way to do it? thx for the quick reply.

[Bill Karwin]

th************* ****@yahoo.com wrote:

Unfortunatley I am not the DBA, or I would have done this first thing.
I already suggested changing it from int to datetime and got shot down
quickly.
Sorry about that. I did not understand that was the situation. How
unfortunate!
So, is there a way to do it? thx for the quick reply.

How about the STR_TO_DATE() function, and use the integer value in place
of the string, so that it implicitly casts it to a string?

SELECT STR_TO_DATE(Dat eSold, '%Y%m%d')
FROM `table`;

Regards,
Bill K.