SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL Date query

Formatting date values returned from MYSQL table to more readable format including time

You can check the date_format tutorial to get value directly from mysql without using PHP side formatting

When we receive date value or date and time value from mysql table, we need to convert them in more readable format based on our requirement. We will use here two PHP functions. The function strtotime() will convert the returned value in mysql date and time format to timestamp. Then we will apply date() function to convert them into our readable format. Here in our example we will keep two fields in the table, one with one DATE field and the other one with DATETIME field. Before this you can read mysql select query on how to get data from a table.

<?
$qt=mysql_query("select * from dt_tb");

//Now let us display the data

while($nt=mysql_fetch_array($qt)){
echo
"$nt[id],".date("d-m-Y : G-i",strtotime($nt[dt])).",$nt[dt2] ";
echo "<br>";
}
?>

This is how the formatting done using strtotime() and date() functions. You can change the date() function formatting based on your requirements. The output of the above script is here.

1,26-10-2004 : 0-00,2005-01-25
2,05-05-2004 : 23-56,2005-06-12
3,08-12-2005 : 13-20,2005-06-06

Here is the sql code to create and fill the table with records

CREATE TABLE dt_tb ( id int(2) NOT NULL auto_increment, dt datetime NOT NULL default '0000-00-00 00:00:00', dt2 date NOT NULL default '0000-00-00', PRIMARY KEY (id) ) TYPE=MyISAM;

# # Dumping data for table `dt_tb` #

INSERT INTO dt_tb VALUES (1, '2004-10-26 00:00:00', '2005-01-25');
INSERT INTO dt_tb VALUES (2, '2004-05-05 23:56:25', '2005-06-12');
INSERT INTO dt_tb VALUES (3, '2005-12-08 13:20:10', '2005-06-06');

Be the first to post comment on this article :


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




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

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