1

I have a database with 6k+ rows and don't want to have to manually convert each date to DATETIME, they are currently in varchar.

They are in the UK format, DD/MM/YYYY.

Currently the date is in a column named datetime which is varchar(12)

I want to convert it to a datetime column named date_new.

How can I do this using an SQL statement

Uyghur Lives Matter
  • 18,820
  • 42
  • 108
  • 144
Robbie Wilson
  • 37
  • 6
  • possible duplicate of [Convert varchar into datetime in SQL Server](http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server) – Richard Erickson May 03 '15 at 01:00
  • 2
    in mysql, use function `STR_TO_DATE(datetime,'%d/%m/%Y')`. usually these strings would be stored in a varchar(10).. – amdixon May 03 '15 at 01:06
  • @amdixon Thanks, I managed to perform an update using this function. Problem Solved :) – Robbie Wilson May 03 '15 at 01:09
  • 1
    @RichardErickson Not a duplicate because linked topic refers to MsSql and this one to MySql. – Alejandro May 03 '15 at 01:15

1 Answers1

1

Solved using

UPDATE table SET date_test = STR_TO_DATE( DATETIME,  '%d/%m/%Y' )
Robbie Wilson
  • 37
  • 6