Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-12
3
Medium Priority
?
445 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 252 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 252 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

721 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