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.
Applying Date functionsFrom the varchar field we can apply all types of date functions by using str_to_date. Here we are using to_days()
Output is here.
Some time we will get data in varchar field after importing table from Excel or CSV file. We can first update / change the data format by using str_to_date and then change the field type to Date field.
Now we can change the date field ( Date_field) from varchar to DATE.
For your practice you can use below sql dump to crate my_table and apply above queries.
Here is the structure and data of the table dt_tb3
This article is written by plus2net.com team.
▼ More on Date & Time functions in SQL