Solved

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

Posted on 2009-04-12
3
399 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
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 37
Add a loading gif while php runs server side 15 66
Generate PDF from MySQL using PHP 3 53
Change static entered data to dynamic with counter 5 35
Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

749 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