SQL PHP HTML ASP JavaScript articles and free scripts to download

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 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


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

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

Be the first to post comment on this article :


Post Comment This is for short comments only. Use the forum for more discussions.
Email( not to be displayed)Privacy Policy

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer