I have a column with values '2015-02-14 12:23 AM' declared as varchar(150), I tried using to_date, convert and cast but not able to change the format. I would need this to filter on specific month/year/day. Thanks for the help

PS: Mysql instance is running on RDS through amazon AWS - not sure if its relevant

CodePudding user response：

I suggest storing dates on proper date data type.

to_date is an Oracle function.

In MySQL, you can use STR_TO_DATE to converts a string to date then use Date_format to give the format you need

SELECT DATE_FORMAT(STR_TO_DATE(dt,'%Y-%m-%d %h:%i %p'),'%m/%Y/%d') 
from test;

Demo

You can use substring with concat without the need of converting to date format:

select concat(substring(dt,6,2),'/', substring(dt,1,4),'/',substring(dt,9,2)) as my_date
from test;

Demo

CodePudding user response：

SELECT CAST( SUBSTRING_INDEX( '2015-02-14 12:23 AM', 
                              ' ', 
                              2
                              ) 
             AS DATETIME
             )

returns the datetime value. If you need to use this value in datetime context then you may remove CAST, it will be performed implicitly:

SELECT TIMESTAMPDIFF ( MINUTE,
                       SUBSTRING_INDEX( '2015-02-14 12:23 AM', 
                                        ' ', 
                                        2
                                        ),
                       '2015-02-14 12:34'
                       )