FRIHOSTFORUMSSEARCHFAQTOSBLOGSCOMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Formatting mySQL date





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.
Related topics
Installing, Partitioning & Formatting HDDs
PHP Tutorial: Basic Shoutbox w/ MySQL
V Bulletin dökümanlarý
MySQL DB Backup script - minor problem
mySQL - Day, date, month, year, time Output/Input
What is the best way to save a date/timestamp in mysql
Openoffice Custom Week Numbers
MySql DATE and ORDERING something by the DATE and displaying
Spanish date functions in php and mysql
How can I check...
Number or date/time field for timestamp?
Check the closest event in Calendar?
MySQL Date Addition with Variable Interval Type
Checkbox and Date filter with mysql and PHP
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

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