xtheonex Posted March 26, 2006 Share Posted March 26, 2006 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 Quote Link to comment Share on other sites More sharing options...
toplay Posted March 26, 2006 Share Posted March 26, 2006 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] Quote Link to comment Share on other sites More sharing options...
redarrow Posted March 26, 2006 Share Posted March 26, 2006 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] Quote Link to comment Share on other sites More sharing options...
xtheonex Posted March 26, 2006 Author Share Posted March 26, 2006 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 Quote Link to comment Share on other sites More sharing options...
toplay Posted March 26, 2006 Share Posted March 26, 2006 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--] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.