MySQL Server Forum - convert int date to datetime

Author

convert int date to datetime

thomasamillergoogle@yahoo.com

2005-06-19, 8:23 pm

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

2005-06-19, 8:23 pm

thomasamillergoogle@
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@yahoo.com

2005-06-20, 3:23 am

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

2005-06-20, 3:23 am

thomasamillergoogle@
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(DateSold
, '%Y%m%d')
FROM `table`;

Regards,
Bill K.

Sponsored Links