如何在mysql中获取字符串作为日期？

I have a table that keeps date and hour data as string. I added two fields date2 and hour2 in order to convert and save current string instead of varchar. Date format is dd.mm.YYYY. I want to convert that string to date format and keep it in corresponding column for each row.

I have read "how to convert a string to date in mysql?" tried to query the following statement:

UPDATE `rawdata` SET `date2` = STR_TO_DATE(`date`, '%Y-%m-%d')

but I still see NULL values in the date2 column. Nothing is updated.

The STR_TO_DATE() function's second parameter specifies the format of the date that you are giving it as a string which in your case does not match the format that is currently in your database. Try using:

UPDATE `rawdata` SET `date2` = STR_TO_DATE(`date`, '%d.%m.%Y')

UPDATE `rawdata` SET `date2` = STR_TO_DATE(`date`, '%Y-%m-%d')

should be according to manual:

UPDATE `rawdata` SET `date2` = STR_TO_DATE(`date`, '%d.%m.%Y')

The second parameter, format, corresponds to how your string is currently formatted, not the format of MySQL date.

Alternatively I would combine date2 and hour2 into a datetime field using the following:

UPDATE `rawdata` SET `datetime2` = STR_TO_DATE(CONCAT(`date`,'.',`hour`), '%d.%m.%Y.%H:%i')

That is of course after creating datetime2 field with datetime data type.