How to fill a datetime field from another field which is int(11) datetime?

[nkamp]

Hello,

I'm trying to upgrade virtuemart from version 1.1.9 to 2.0.xx.

Now the virtumart pricetable is not converted right.
In the version 1.1.9 there two fields exist cdate and mdate. Both int(11) fields, but the value is date.

Now I have created a new 'pricetable_new' and add some field etc. Both tables are in the same database.

In the new version 2.0.xx the fields cdate and mdate do not exist anymore and are renamed to created_on and modified_on and both are datetime fields!!

So my question is:
- how do I convert this int(11) value to a datetime?
- and next how do I populate the new 'pricetable_new' with the value's of cdate to the field created_on (mdate to modified_on) as datetime format.

For me it's not a problem if it is one sql statement.

Nico

[Barand]

It depends on how the numeric date is stored . Is it

• 20140502, or
• 1398985200 (unix time)

This example shows how to convert both (numdate and unixtime are INT, the realdate1/2 are DATETIME)

mysql> SELECT * FROM test.datetest;
+----+----------+------------+---------------------+---------------------+
| id | numdate  | unixtime   | realdate1           | realdate2           |
+----+----------+------------+---------------------+---------------------+
|  6 | 20140502 | 1398985200 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+----------+------------+---------------------+---------------------+

UPDATE datetest
SET 
    realdate1 = numdate
    , realdate2 = FROM_UNIXTIME(unixtime);

+----+----------+------------+---------------------+---------------------+
| id | numdate  | unixtime   | realdate1           | realdate2           |
+----+----------+------------+---------------------+---------------------+
|  6 | 20140502 | 1398985200 | 2014-05-02 00:00:00 | 2014-05-02 00:00:00 |
+----+----------+------------+---------------------+---------------------+