We 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 format. You can read all the date formats on how to use formatted 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.
Applying Date functions
From the varchar field we can apply all types of date functions by using str_to_date. Here we are using to_days()
SELECT to_days( str_to_date( dt3, '%m-%d-%Y' ) ) AS my_date FROM dt_tb3
Output is here.
my_date
732336
732474
NULL
NULL
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.
update `my_table` set Date_field =STR_TO_DATE( Date_field, '%d-%M-%Y' )
Now we can change the date field ( Date_field) from varchar to DATE.
ALTER TABLE `my_table` CHANGE `Date_field` `Date_field` DATE NULL DEFAULT NULL
For your practice you can use below sql dump to crate my_table and apply above queries.
CREATE TABLE IF NOT EXISTS `my_table` (
`Date_field` varchar(11) DEFAULT NULL,
`Close` decimal(7,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `my_table`
--
INSERT INTO `my_table` (`Date_field`, `Close`) VALUES
('2-May-2018', '17590.86'),
('3-May-2018', '17554.22'),
('4-May-2018', '17460.34'),
('7-May-2018', '17606.72'),
('8-May-2018', '17610.81'),
('9-May-2018', '17662.33'),
('10-May-2018', '17625.79'),
('11-May-2018', '17770.55'),
('14-May-2018', '17781.01'),
('15-May-2018', '17774.62'),
('16-May-2018', '17696.59'),
('17-May-2018', '17577.21'),
('18-May-2018', '17426.80'),
('21-May-2018', '17310.72'),
('22-May-2018', '17328.27'),
('23-May-2018', '17175.11'),
('24-May-2018', '17334.21'),
('25-May-2018', '17465.09'),
('28-May-2018', '17585.39'),
('29-May-2018', '17477.27'),
('30-May-2018', '17455.71'),
('31-May-2018', '17663.84'),
('1-June-2018', '17616.28'),
('4-June-2018', '17508.60'),
('5-June-2018', '17454.26'),
('6-June-2018', '17592.09');
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');
← Subscribe to our YouTube Channel here