How to change a Text to Date using MYSQL Workbench (1 Viewer)

sbrown106

Member
Local time
Today, 14:39
Joined
Feb 6, 2021
Messages
77
Hi - could somebody help me with this please, I'm new to workbench and Ive just uploaded a table into workbench from an excel csv file. In the excel table
one of the columns had a date value, I loaded this into my table using workbench as a text file as I was having problems importing this file using the wizard because it was having problems with importing the date, so in my new table in workbench this date field (text) is displayed as dd/mm/YYYY (eg 01/01/1999)

So in the example below if;

table - mytable
date field - fldmydate

In mytable if my date field is mydate in the format eg 06/08/2021 which has been stored as text in mytable in workbench

I want to change this text field to a date field in the format 'dd/mm/YYYY' and I use the

UPDATE mytable SET fldmydate = STR_TO_DATE(fldmydate,'%d-%m-%Y');

15:53:25 UPDATE mytable SET fldmydate = STR_TO_DATE( fldmydate'%d-%m-%Y') Error Code: 1411. Incorrect datetime value: '06/08/2021' for function str_to_date 0.016 sec

Could somebody please help with what I'm doing wrong - I cant see an error
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:39
Joined
Feb 19, 2013
Messages
16,555
Haven't heard of workbench for years - it used to be for .mdb's did it get upgraded for .accdb's?

and not heard of a str_to_date function presume it is a workbench function, I would use the cDate function

UPDATE mytable SET fldmydate = cDate(fldmydate)

however a field can only be one datatype - you seem to be trying to change it to a date field from a text field. You should create a new field called say newMyDate as a date and populate that instead

UPDATE mytable SET newMyDate = cDate(fldmydate)

then you can delete the old field and rename the new one

Edit: just realised you are talking about mySQL, not Access, so you probably need to ignore this post :(
 

sbrown106

Member
Local time
Today, 14:39
Joined
Feb 6, 2021
Messages
77
Haven't heard of workbench for years - it used to be for .mdb's did it get upgraded for .accdb's?

and not heard of a str_to_date function presume it is a workbench function, I would use the cDate function

UPDATE mytable SET fldmydate = cDate(fldmydate)

however a field can only be one datatype - you seem to be trying to change it to a date field from a text field. You should create a new field called say newMyDate as a date and populate that instead

UPDATE mytable SET newMyDate = cDate(fldmydate)

then you can delete the old field and rename the new one

Edit: just realised you are talking about mySQL, not Access, so you probably need to ignore this post :(
It was MySQL but I followed your advice about creating a new field for the date and then using something similar to what you said above but for sql and it worked- so thanks for that
 

Users who are viewing this thread

Top Bottom