Solved

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

Posted on 2009-04-12
3
420 Views
Last Modified: 2013-12-12
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
Comment
Question by:Ansolon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 21

Accepted Solution

by:
K V earned 63 total points
ID: 24128287
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
 
LVL 14

Assisted Solution

by:racek
racek earned 63 total points
ID: 24128509
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question