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.
SELECT date_format( str_to_date( dt3, '%m-%d-%Y' ) , '%m/%d/%Y' ) AS my_date FROM dt_tb3Note 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.
|▼ Date & Time functions in SQL|
|If i get null in a field as result of SELECT date_format( str_to_date( dt3, '%m-%d-%Y' ) , '%m/%d/%Y' ) AS my_date FROM dt_tb3. |
How can I resolve this?