Solved

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

Posted on 2009-04-12
3
406 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:
theGhost_k8 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

737 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