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

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

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

Subhendu Mohapatra