Jump to content

Reformat "datetime" brought from mysql db


xtheonex

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,
Dave
Link to comment
Share on other sites

from the manual good luck.


[code]

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
?>
[/code]
Link to comment
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
Link to comment
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--]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.