MySQL - Change Date String to Date Type in Place

MySQL - Change date string to date type in place?

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');

How to convert a string to date in MySQL?

As was told at MySQL Using a string column with date text as a date field, you can do

SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y')
FROM    yourtable

You can also handle these date strings in WHERE clauses. For example

SELECT whatever
  FROM yourtable
 WHERE STR_TO_DATE(yourdatefield, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAY

You can handle all kinds of date/time layouts this way. Please refer to the format specifiers for the DATE_FORMAT() function to see what you can put into the second parameter of STR_TO_DATE().

convert full date string to date mysql

All you have to do is change small letter m to big letter M in your str_to_date function.

select STR_TO_DATE('22 Jan 2019 11:03','%d %M %Y');

so the final query would be:

select DATE_FORMAT(STR_TO_DATE('22 Jan 2019 11:03','%d %M %Y') ,'%d-%m-%Y');

Here is a demo

Converting a date in MySQL from string field

This:

STR_TO_DATE(t.datestring, '%d/%m/%Y')

...will convert the string into a datetime datatype. To be sure that it comes out in the format you desire, use DATE_FORMAT:

DATE_FORMAT(STR_TO_DATE(t.datestring, '%d/%m/%Y'), '%Y-%m-%d')

If you can't change the datatype on the original column, I suggest creating a view that uses the STR_TO_DATE call to convert the string to a DateTime data type.

Convert different string date, into one single date format In mysql

You can case when with regular expression

SELECT
CASE WHEN create_date REGEXP [0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] THEN STR_TO_DATE(create_date, '%m-%d-%Y')
WHEN create_date REGEXP Format THEN STR_TO_DATE(create_date, '%m/%d/%Y')
ELSE create_date
END AS create_date
FROM TABLE_NAME

https://www.guru99.com/regular-expressions.html

MySQL - Change date string to date type in column/database

A possible solution

UPDATE Table1
   SET dispatchdate = DATE_FORMAT(STR_TO_DATE(dispatchdate, '%d/%m/%Y'), '%Y-%m-%d');

ALTER TABLE Table1 
      CHANGE dispatchdate dispatchdate date;

Here is SQLFiddle demo

Convert text data type to date

The built-in MySQL function you are looking for is str_to_date(). You can pass this function a string containing a date, in a specified format, and it will return a datetime value. See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format for format specifiers.

example:

select str_to_date('08/28/2021', '%m/%d/%Y');

returns:

2021-08-28

Convert string date name to datetime or string date

With STR_TO_DATE() you can convert String to Date and with DATE_FORMAT() format your Date value.

select DATE_FORMAT(STR_TO_DATE('19-Oct-19', '%Y-%M-%d'), '%d-%m-%Y');

How to convert text datatype to datetime in mysql?

If the original data is not in MySQL Datetime format (YYYY-MM-DD HH:MM:SS), you cannot just change the column datatype from Varchar/Text to Date/Datetime. Otherwise, there will be an irreparable Data loss.

This will be a multi-step process. You will first need to convert the date string to MySQL date format (YYYY-MM-DD HH:MM:SS). We can use STR_TO_DATE() function for this.

Your sample date string (18-11-15 18:21:25) is basically in %y-%m-%d %T format. Following format specifiers can be used:

• %d Day of the month as a numeric value (01 to 31)
• %m Month name as a numeric value (00 to 12)
• %y Year as a numeric, 2-digit value
• %T Time in 24 hour format (hh:mm:ss)

The query to update the date would look as follows:

UPDATE invoices  
SET created = STR_TO_DATE(created, '%y-%m-%d %T');

Now, you can use Alter Table to change the data type from Text type to Datetime.

ALTER TABLE invoices 
MODIFY COLUMN created datetime;

---

Related Topics