Formatting string data stored in Varchar field to Date formats

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');

SQL Date References Storing date in MySQL date field

Subscribe to our YouTube Channel here

Alex	26-06-2015
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?

Formatting Date data stored in varchar field

Applying Date functions

Subscribe