Formatting Date data stored in varchar field |
We have seen how to format the returned date filed 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_tb3
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
01/25/2005
06/12/2005
NULL
NULL
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
CREATE TABLE `dt_tb3` (
`id` int(2) NOT NULL default '0',
`dt3` varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `dt_tb3` VALUES (1, '01-25-2005');
INSERT INTO `dt_tb3` VALUES (2, '06-12-2005');
INSERT INTO `dt_tb3` VALUES (3, '2005-06-06');
INSERT INTO `dt_tb3` VALUES (5, '2006-01-02');
|
