Jump to content


This topic is now archived and is closed to further replies.


Reformat "datetime" brought from mysql db

Recommended Posts

Hi guys.
This is probably really simple but i started learning php/mysql about two years ago. Had a "break" for a while and now im coming back to it with a couple of projects i need a little help if you can spare the time.

I have a mysql database with a "datetime" colum.
When i fetch the string from the database it comes in an "unfriendly format. I'd Like to be able to firstly, split the time and date into separate strings. And also reverse the date format.
The format it is fetched from, and stored in the database is yyyy-mm-dd hh:mm:ss however on fetching it from the database i'd like to be able to split the date and time, and reverse the date format so its dd-mm-yyyy.
If anyone can spare some time to explain how to do it id be very grateful.

Thanks in advance,

Share this post

Link to post
Share on other sites
Use the MySQL date_format() function. See:

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#id3201971\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/dat....html#id3201971[/a]

Share this post

Link to post
Share on other sites
from the manual good luck.


My function (http://phpshortcuts.blog-city.com/dateconvert_1.htm
) for converting system dates into user-friendly dates depending on the scenario in which it's used:

function DateConvert($old_date, $layout)
//Remove non-numeric characters that might exist (e.g. hyphens and colons)
$old_date = ereg_replace('[^0-9]', '', $old_date);

//Extract the different elements that make up the date and time
$_year = substr($old_date,0,4);
$_month = substr($old_date,4,2);
$_day = substr($old_date,6,2);
$_hour = substr($old_date,8,2);
$_minute = substr($old_date,10,2);
$_second = substr($old_date,12,2);

//Combine the date function with mktime to produce a user-friendly date & time
$new_date = date($layout, mktime($_hour, $_minute, $_second, $_month, $_day, $_year));
return $new_date;

The function is then called like this:

$todays_date = "2005-07-01 12:35:01";

//We can use words to desccribe the date (as you see on on my blog)
print DateConvert($todays_date, "l, jS F Y");//Friday, 1st July 2005

//We can use the UK style
print DateConvert($todays_date, "d/m/Y");//01/07/2005

//We can use the US style
print DateConvert($todays_date, "Y/m/d");//2005/07/01

//We can even add the time
print DateConvert($todays_date, "Y/m/d - g:ia");//2005/07/01 12:35pm

Share this post

Link to post
Share on other sites
Thanks guys.

toplay, unfortunatley, im not very well versed with manual mysql type stuff. I usually use PHPMyAdmin to administer my databases. Its a cheats way i know but it works just fine for me at the moment. I will learn to use mysql manually one day but im concentrating more on using it with php to make scripts and such like.

redarrow. The function works perfectly and is exactly what i was looking for. Cheers :D

Share this post

Link to post
Share on other sites
I'm not sure what you mean by "manual", and then say you're learning to use SQL with PHP. Do whatever you wish.

I personally find the below SQL example is easier to retrieve date and time than that PHP function posted. If SQL has the functions to do something, it's best to utilize it instead of making PHP do it.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DATE_FORMAT(date_column, [color=red]'%d[span style=\'color:orange\']-[/color]%m[color=orange]-[/color]%Y'[/span]) [color=green]AS[/color] formatted_date, DATE_FORMAT(date_column, [color=red]'%r'[/color]) [color=green]AS[/color] formatted_time [color=green]FROM[/color] [color=orange]table_name[/color] [!--sql2--][/div][!--sql3--]

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.