How to convert a varchar column type to date type without losing the dates

Question

I am looking for a way to change the datatype of a column. Currently, in my database, the date columns types were defined as varchar and I need to convert them back to the date type.

Any idea how to do it?

Answer 1

You will need to adapt this based your your exact table structure but something like;

CREATE TABLE temp (startdate varchar(255), stuff varchar(255));

INSERT INTO temp
SELECT startdate,stuff
FROM mytable;

TRUNCATE TABLE mytable;

ALTER TABLE mytable ALTER COLUMN startdate DATETIME NOT NULL;

INSERT INTO mytable
SELECT CAST(startdate AS DATETIME), stuff FROM temp;

DROP TABLE temp;

Answer 2

First, create the new column with type data Next, run update query, to populate the new column with the value of the old one, applying any conversion if needed Next, drop the old column Finally, rename the new column to the old one

Answer 3

1. Create a new DATE column with a temporary name
2. Populate the new column with an UPDATE query that makes use of STR_TO_DATE()
3. If everything's right, remove the VARCHAR column and rename the DATE column.

Answer 4

Mysql default date format is : YYYY-MM-DD . If your try to insert the date otherwise, as you actually did, the date will be inserted with these values : 000-00-00, giving you a hint to the acceptable date format for mySql.

Answer 5

Wanna share this for SQL server users. For me this method is much convenient and safer.

1. In your Table create new column "NewDate" (temporarily or name whatever you want).

2. Make sure no invalid Datetime format in the Table you want to convert. Try those formats here: https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_cast3 <--- you need to check thoroughly otherwise there would be an error executing the command below.

3. Execute this command:

UPDATE myTable SET NewDate = CAST(OldDate AS datetime) WHERE (OldDate <> '') AND (OldDate IS NOT NULL) --to make sure you cast only what is needed otherwise there would be an error.

4. You can now delete the old column i.e. "OldDate".
5. Finally you can drag and drop the new table you've created to the slot where you just deleted the old column in the table design.

Answer 6

If the field of your column is VARCHAR and stored date as DD-MM-YYYY then we have to convert the date in YYYY-MM-DD format by following PHP code.

$cd = array();

$cd1 = array();

$cdf = array();

$getdata = mysqli_query($link,"SELECT columnname FROM tablename");

while($row=mysqli_fetch_array($getdata))        
{   
    $cd = $row['columnname'];

    $cd1 = strtotime($cd);

    $cdf = date('Y-m-d',$cd1);

    mysqli_query($link,"UPDATE tablename SET columnname = 
    REPLACE(columnname,'$cd','$cdf')");
}

After running this PHP code, in your MySQL table change the datatype of your column to 'DATE'. It works for me without losing or truncate data.