Formatting Date data stored in varchar fieldWe have seen how to format the returned date field data by using date_format() function in mysql. Here date value is stored in date fields of mysql table. Some time date values are stored in varchar fields so we can't use date_format() function to format these returned date values as per our requirements.
Here we can use str_to_date() function first to convert the string of date value and then apply date_format function to display date in our required format.
Let us try with one example.
In out table dt3 is a varchar field of dt_tb3 table and some date data are stored. Note that dt3 field is not a date field. We will apply the following query to get the data in date fomat. You can read all the date formats on how to use fomatted dates.
Note that we have applied the date_format function after changing the string data to date format by using str_to_date function.
Here is the output
Last two records has returned NULL value as the data stored here are not matching the format given inside the str_to_date function.
Here is the structure and data of the table dt_tb3