FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

Formatting mySQL date

   Frihost Forum Index -> Scripting -> Php and MySQL
 


garionw
I'm wondering if someone is able to help me please. I am wanting to format the date stored in mysql database so that it looks readable to my users. This is how its stored:
Code:
2007-01-12 18:43:58


And this is how I would like it to be stored (If possible ) Wink
Code:
Friday, 1st January 2007 6:43pm



Thanks in advance,
Garion
MrBlueSky
Check mysql's DATE_FORMAT and TIME_FORMAT functions:

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

(Use the browsers find-function to find 'DATE_FORMAT')

Look at the examples given there, they should help you to get started:

Quote:

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
garionw
Thanks, I had a look on the site but found it a little hard to put into practice. Heres the mySQL select query I have so far:

Code:
$result = mysql_query("SELECT * FROM gwplus_blog_posts WHERE post_blog = 1 ORDER BY post_id DESC LIMIT 0 , 1");


Now thats being used in conjunction with a mysql_fetch_array. But that will give me a Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\www\index.php on line 40 error.

What would be the best place to place the DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') code (In that mySQL query)

Thanks in Advance,
Garion
hexkid
garionw wrote:
Heres the mySQL select query I have so far:

Code:
$result = mysql_query("SELECT * FROM gwplus_blog_posts WHERE post_blog = 1 ORDER BY post_id DESC LIMIT 0 , 1");

Don't "SELECT * ..."; Do "Select post_id, post_blog, poster_id, date_format(date_posted, '%W %M %Y'), ..."

garionw wrote:
Now thats being used in conjunction with a mysql_fetch_array. But that will give me a Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\www\index.php on line 40 error.

You should check if the query was successful
Code:
$result = mysql_query("SELECT post_id, post_blog, poster_id, date_format(date_posted, '%W %M %Y') FROM gwplus_blog_posts WHERE post_blog = 1 ORDER BY post_id DESC LIMIT 0 , 1");

### check if the query was successful
if ($result === false) {
    ### it was not successful
    ### for simplicity sake, just show the error and exit
    exit('Query error: ' . mysql_error());
}
MrBlueSky
I have no experience with DATE_FORMAT, so I don't know right know

But, here are some examples:

http://www.desilva.biz/mysql/formatdate.html

http://www.phpfreaks.com/mysqlref/3.php
garionw
MrBlueSky wrote:
I have no experience with DATE_FORMAT, so I don't know right know

But, here are some examples:

http://www.desilva.biz/mysql/formatdate.html

http://www.phpfreaks.com/mysqlref/3.php



Thanks for your help, heres what I had to add:
Code:
DATE_FORMAT(post_date,'%W %D %M %Y at %l:%i %p') AS mydate
and then $row['mydate'] with $row being the fetch array

This topic could be locked now.
hexkid
garionw wrote:
heres what I had to add:
Code:
DATE_FORMAT(post_date,'%W %D %M %Y at %l:%i %p') AS mydate
and then $row['mydate'] with $row being the fetch array

Ah! I always use mysql_fetch_row() and then numeric indexes for the $row array, so I never remember to alias the columns.
Hope it'll stick this time.
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.