making Date Mon in one column equal to yyyy-mm-dd hh:mm:ss

user_14331922 0
Viewed 45

In my table i'm having two fields created_date and created_month.

  • created_date: DATETIME e.g. 2020-12-17 17:23:56
  • created_month: VARCHAR e.g. 17-Aug

Now the situation- suppose for a record the created_date is 2020-12-17 17:23:56 and created_month is 17-Aug.

I am trying to achieve that the created_date should become like this 2020-08-17 17:23:56. The month from created_month gets updated in created_date. Not very much particular about time or date (17) but year yes. If I'm doing this UPDATE my_table SET created_date = STR_TO_DATE(created_month, '%d %b') It is giving this 0000-08-12 00:00:00 . the year is 0000

mysql
1 Answers

put created_month in created_date without altering the year

UPDATE my_table 
SET created_date = STR_TO_DATE(CONCAT(created_month, 
                                      '-', 
                                      YEAR(created_date), 
                                      ' ', 
                                      SUBSTRING_INDEX(created_date, ' ', -1)), 
                               '%d-%b-%Y %H:%i:%s');

fiddle

user_101387340
Related