Date Format Question

[MarkB]

I'm using the snippet of code Freddie posted here (https://vborg.vbsupport.ru/showthread.php?s=&threadid=24939) to get the date format:

SELECT DATE_FORMAT(eventdate,'%b-%d') AS eventdate

It outputs the date as (eg) Apr-18

Can I make it display as April-18 (ie, full month name)?

[Neo]

Instead of %b use %M

[MarkB]

Thanks neo :)

Now another question. I have this code:


<?PHP
require("./global.php");
$events = $DB_site->query("SELECT eventid, DATE_FORMAT(eventdate,'%M %d') AS eventdate, subject, venue, location FROM calendar_events WHERE public='1' ORDER BY eventdate LIMIT 10");
while ($event = $DB_site->fetch_array($events)) {
echo "<font size=1 face=\"verdana, helvetica\">" . $event['eventdate'] . " <a href=\"$bburl/calendar.php?s=&action=getinfo&eventid=" . $event['eventid'] . "\">" . $event['subject'] . "</a> " . $event['location'] . "</font>";
}
?>


It spits out the dates fine, but since I have it ordered by date, it lists the months alphabetically instead of by calendar order... Is there a way to fix that? :ermm:

[MarkB]

You can see the output here:

http://www.ultimatemetal.com/forum/events2.php

[Neo]

There are two ways I know of doing it. Either add another row like "monthnum" and sort by that or, change the %M so that it reads in the date like 0-5-3 and then use MONTHNAME which would output May.

[MarkB]

:ermm:

Where would I put MONTHNAME?

[Neo]

Hmm well it would have to be in a different query maybe. BUt your best shot as far as I know would tp just enter the month number aswell for sorting purposes.

[MarkB]

I worked it out - thanks for pointing me in the right direction :) I changed the code to:

SELECT eventid, DATE_FORMAT(eventdate,'%M %d') AS date, eventdate, ...

And then just changed the later $event['eventdate'] to $event['date'] - so, it lists the month but sorts by the correct date :)

Thanks Neo :D

[MarkB]

Now to try and find out how to make each row have an alternating background colour (like the posts in showthread) :)

[Neo]

Well have fun :)
I am off the the old sleep

[MarkB]

Thanks again :)

[EZwriter]

Hi,

I'm having a bit of trouble with formatting the date from the thread table. I am getting NULL as a result from this query:

select date_format(lastpost, '%y-%m-%d') as formatted_date from thread limit 10;

Anyone know what's up?

[EZwriter]

Never mind...

FWIW, instead of using DATE_FORMAT, the correct option is to use FROM_UNIXTIME.

:rambo: