Formatting Date data stored in varchar field

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


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    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?

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer