Formatting a date value

[geroid]

I'm trying to format a date extracted from my table. The date is in the following format 2009-11-18 which is typical. I want it to display on the webpage as November 18 2009. I can't use the date_format function directly in the query because I'm comparing the date with a variable for selection during the query. So this means I have to format the date field (event_date) after it has been retreived from the table. I tried the following code and it works to a degree

 

$row->event_date = date("M j Y" ,strtotime($row->event_date));

 

The above code returns Nov 18 2009 but I want the full month as in November 18 2009. Any ideas? I just want to convert the $row->event_date to the format November 18 2009 (or whatever the date may be)

[geroid]

Maybe the strtotime function is not the right one to use. I just want to format that date in row->event_date to a nice look

[PFMaBiSmAd]

You can put ANYTHING you want in the SELECT list in a query -

 

SELECT *, DATE_FORMAT(your_column, 'actual format string') as formated_date FROM your_table ...

 

If you are fetching the row as an object, you would access the result in the above as $row->formated_date

[Mchl]

Apart from what PFMaBiSmAd said, the manual entry for date has a nice table with all format characters explained.

[geroid]

Hi

Thanks for the replies. I had previously had my query like this

$query = "select event_num, event_name, venue_name, streetaddress1, town, county, event_description_ire, event_description_eng,  date_format(event_date, '%M %D, %Y ') as formatteddate, event_time from $table  where event_date >= '$todaydate' order by event_num desc";

but I changed it to this:

 

$query = "select event_num, event_name, venue_name, streetaddress1, town, county, event_description_ire, event_description_eng,  event_date, event_time from $table where event_date >= '$todaydate' order by event_num desc";

 

I would like to use the date_format function but when I used it the query didn't retrieve the date. It could not compare the formatted date (November 18 2009) with the contents of the variable $todaydate (2009-11-18). Any suggestions how to get around this problem

[cags]

In answer to your original question (basically the information Mchl linked to)...

 

date("F j Y" ,strtotime($row->event_date));

 

As for more recently I believe when PHP retrieves dates from MySQL they get returned as a string. So in order to compare it to a PHP date object you would have to first convert it to a date object using strtotime (as you were doing already). So if you are doing any comparison with the date after fetching it, there's IMHO no real point formatting it with MySQL. But if you don't need to compare it you can convert it with  MySQL.

 

Since you are fetching items from your array based on the date though, I don't see why you'd need to compare it afterwards. So the option suggested by PFMaBiSmAd should be fine.

 

$query = "
SELECT event_num, event_name, venue_name, streetaddress1, town, county, event_description_ire, event_description_eng,  DATE_FORMAT(event_date, 'actual format string') AS formatted_date, event_time 
FROM $table 
WHERE event_date >= '$todaydate' 
ORDER BY event_num desc";

[fenway]

MySQL also has DATE_FORMAT()