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

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.

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


Google+

Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



Join Our Email List
Email:  
For Email Newsletters you can trust

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

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