• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

MYSQL VARCHAR(20) TO DATE TIME WITHOUT LOOSING DATA IN IT

Hi,
I am moving a data structure Varchar(20) to DATETIME. I did it before over 3 months ago and I remember simple phpmyadmin changing also changed date type and I see the real dates not 0000-00-00 00:00:00.

Here it is the structure information which I want to convert

Field: user_regdate
Type: VARCHAR
Lenght/Values: 20
Collation:  utf8_unicode_ci
Attributes:
Null: not null
Default:
Extra:
Comment

And While we are browsing the date seems Feb 25, 2002

I want to convert this without loosing data are like "Feb 25, 2002" to DATETIME, for example 2002-02-25 00:00:00.

Thew new strcuture will be.

Field: user_regdate
Type: DATETIME
Lenght/Values:
Collation:  
Attributes:
Null: not null
Default: 0000-00-00 00:00:00
Extra:
Comment

I am waiting for answer and solutions very thanks
0
Ansolon
Asked:
Ansolon
2 Solutions
 
K VDatabase ConsultantCommented:
You first have to update varchar field to some proper date format:
like
update TABLE SET str_date_field= DATE_FORMAT( str_to_date(str_date_field,'%b %d, %Y'),'%d-%m-%Y %H:%i:%s') ;

This will set all fields like 'Feb 25, 2002' to '25-02-2002 00:00:00'.
Then if u convert it to date-time, it wont get affected.
0
 
racekCommented:
1. add new column:
 ALTER TABLE your_table ADD user_regdate2 DATETIME ......

2 update new colummn
update TABLE SET str_date_field= DATE_FORMAT( str_to_date(user_regdate,'%b %d, %Y'),'%d-%m-%Y %H:%i:%s') ;

3. Control
SELECT COUNT(*) tot_number,
SUM(IF(user_regdate2 = DATE_FORMAT( str_to_date(user_regdate,'%b %d, %Y'),'%d-%m-%Y %H:%i:%s'),1,0)) matching_columns
from your_table;

4. if all columns are ok drop old column
ALTER TABLE your_table DROP COLUMN user_regdate;

5. Rename new column to old column name
ALTER TABLE your_table
    CHANGE COLUMN user_regdate2 user_regdate
   DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00';



0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now