MySQL - Change date string to date type in place?
18,476
Solution 1
You probably want to use the STR_TO_DATE()
function.
SELECT STR_TO_DATE(textdate, '%d/%m/%y') FROM MyTable...
Or create another column with DATE
data type and copy the values:
ALTER TABLE MyTable ADD COLUMN realdate DATE;
UPDATE MyTable SET realdate = STR_TO_DATE(textdate, '%d/%m/%y');
Solution 2
UPDATE your_table
SET your_col = '20'
+ substring(your_col,
LOCATE('/', your_col, LOCATE('/', your_col) + 1) + 1
)
+ '-'
+ substring(your_col,
LOCATE('/', your_col) + 1,
LOCATE('/', your_col, LOCATE('/', your_col) + 1)
)
+ '-'
+ substring(your_col, 1, LOCATE('/', your_col) - 1)
/*
LOCATE('/', your_col) -> search for 1st occurence of '/' in your_col
LOCATE('/', your_col, LOCATE('/', your_col) + 1) -> search for 2nd occurence of '/' in your_col
LOCATE('/', your_col, LOCATE('/', your_col) + 1) + 1 -> from where to retriev the last part
*/
(I suppose year >= 2000)
Author by
SirRatty
Updated on June 29, 2022Comments
-
SirRatty almost 2 years
(Hi all. I'm new-ish to SQL and MySQL in particular. I know some PHP.)
I have a table which contains a "date_string" column. For legacy reasons, this is a text field, containing the date in d/m/YY format. e.g. "22/11/09" for 22nd November 2009.
Question: how could I convert all fields in this column to a standard MySQL date format (YYYY-mm-dd), in-place?
Or, failing being able to change it in place, given that the column type is text, how could I take each existing text-based date, and create a standard date in another column (of type 'date') within the table?
Thanks for any help.
-
SirRatty almost 15 yearsThank you for the help! When I try either option, I get "NULL" as the results for all fields. Specifically, if I do this: SELECT STR_TO_DATE('%d/%m/%y', date_string) FROM myTable Then I get a list of NULL values, one for each record. "date_string" is of varchar type, 32 chars max. Dates are currently stored as "19/3/05", "1/8/07", etc.
-
SirRatty almost 15 yearsThank you. Yes, the year in this case is always >= 2000. However, the strings themselves are of variable length, as "1/1/01" has been recorded along with dates like "31/12/09". Would both dates survive your algorithm? Cheers.
-
Bill Karwin almost 15 yearsSorry, I got the arguments reversed. I'll edit the above examples to be correct.
-
manji almost 15 years1st version will not apply to '1/1/01'. Amended. Now it will work for both.