Page 1 of 2
Date display format help (New to forum)
Posted: Mon Aug 16, 2004 11:35 am
by Deno
Hello,
Just wanna introduce myself to this forum first of all, as this is my first post ever... on this forum. So, Hi everyone!
Now to my problem....
I'm trying to retrieve data from a MySQL DB, where one of the fields is an event date, where it displays it as ex.) 16-08-04, but I want it to display it as example: Monday, August 16th, 2004. How do I do that?
Any help would be really appreciated. Thank you.
n00b to n00b
Posted: Mon Aug 16, 2004 11:52 am
by neophyte
This is how I did it:
"SELECT date_format(link_time, '%d %M %Y at %r') as fmt_link_time FROM dbtable";
Posted: Mon Aug 16, 2004 12:03 pm
by Deno
what's fmt_link_time?
Posted: Mon Aug 16, 2004 12:17 pm
by hawleyjr
fmt_link_time is an identifier for date_format(link_time, '%d %M %Y at %r')
Posted: Mon Aug 16, 2004 12:27 pm
by pickle
Another way to do it in solely PHP would be:
Code: Select all
$date = date retrieved from db
$exploded = explode("-",$date);
$stamp = mktime(0,0,0,$exploded[1],$exploded[0],$exploded[2]);
$pretty_date = (date('l, F jS, Y',$stamp); //the "l" is a lowercase "L"
This will also put the "th", "nd", and "st" after the day of the month - I'm not sure if the SQL way will do that.
PHP Manual:
[php_man]explode[/php_man]
[php_man]mktime[/php_man]
[php_man]date[/php_man]
Yay Alberta!!!!
Posted: Mon Aug 16, 2004 2:12 pm
by Deno
Thanks everyone!!!

I'll try it and see what happens!
Yes, Alberta!!
Posted: Mon Aug 16, 2004 4:42 pm
by Deno
Ok, I'm getting parse errors on both methods... For the first method, I'm trying to select one of the fields called eventdate; how would it look like?
Posted: Mon Aug 16, 2004 4:57 pm
by pickle
What's your code look like for my method - that's really the only one I'm qualified to debug

Posted: Mon Aug 16, 2004 5:14 pm
by Deno
Here's my code:
Code: Select all
<?php
while ($Row = mysql_fetch_array($Result)) {
$date = date($Row[eventdate]);
$exploded = explode("-",$date);
$stamp = mktime(0,0,0,$exploded[1],$exploded[0],$exploded[2]);
$pretty_date = (date('l, F jS, Y',$stamp);
print("<table width='80%' border='1' cellpadding='0' cellspacing='0' bordercolor='#FF66CC'><tr>");
print("<td bgcolor='#9933CC'>");
print("<b><font color='#FFFF00'>$Row[eventname]</font></b><p>");
print("<b>Date:</b>$pretty_date<br>");
print("<b>Price:</b>$Row[price]<br>");
print("<b>$Row[title]:</b> $Row[person]<p>");
print("$Row[details]<P><br><br>");
print("</td></tr></table><p>");
}
?>
It keeps pointing to this line:
Code: Select all
<?php
$pretty_date = (date('l, F jS, Y',$stamp);
?>
Posted: Mon Aug 16, 2004 5:16 pm
by markl999
$pretty_date = (date('l, F jS, Y',$stamp);
Remove the first (
future dates pop up...
Posted: Mon Aug 16, 2004 5:19 pm
by Deno
Ok, I removed the first bracket, but now I get dates popping up as January 24th, 2007 , March 15th, 2033, etc... how do I remove that? I just want events for the CURRENT YEAR!

Posted: Tue Aug 17, 2004 9:33 am
by pickle
Well, you could find out what the current year is, and only return dates between 01-01-CURRENT_YEAR and 31-12-CURRENT_YEAR. Or I believe MySQL has a substring function (the sites down for some reason now) that could allow you to ensure the last two digits are of the current year. There might be some date functions in MySQL that are more suited to this as well.
Posted: Tue Aug 17, 2004 11:14 am
by Deno
Code: Select all
<?php
select * from events where MONTH(eventdate) = MONTH(CURRENT_DATE) and YEAR(eventdate) = $current_year order by eventdate asc
?>
Well, that was my query above, and I got those strange years in the first place. I also used your explode syntax too.

Posted: Tue Aug 17, 2004 11:24 am
by pickle
Hmm, display the query with all variables parsed. Just echo it. Where is CURRENT_DATE defined? Is it a global variable or something in MySQL. If it's a global, it won't be parsed if you're in heredocs or quoting the query with ".
Posted: Tue Aug 17, 2004 12:26 pm
by Deno
the CURRENT_DATE is something in MySQL.