Date Format

[Joseph Witchard]

If selecting from a DATETIME field in MySQL, could someone show me how exactly a SQL query looks when you use MySQL's DATE_FORMAT?

[PappaJohn]

Here (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format) is a list of all the format specifiers and some usage examples.

[Joseph Witchard]

I've seen that page. The thing is, I need to get out a date that was inserted from a SQL query on another page. The query that I'm going to use DATE_FORMAT for is also pulling out integers and strings.

[PappaJohn]

Well, without seeing your table structure or the desired format for the date, this is only an example:


$sql = "SELECT DATE_FORMAT(column_name_here, '%b %e, %Y') as date_out FROM table_name_here WHERE where_clause_here";

$result = mysql_query($sql);

if($result)
{
$row = mysql_fetch_assoc($result);

$the_date = $row['date_out'];

echo $the_date; // will echo the date as Nov 28 2008
}

[Joseph Witchard]

I haven't coded the script completely, but here is the query so far:

$query = "SELECT `author_id`, `author_name`, `date_posted`, `category_id`, `categories.category_name`, `title`, `post_body` FROM `posts` JOIN `categories` ON `posts.category_id` = `categories.category_id`";

[guelphdad]

don't code in php. solve your problem first directly in sql and then convert to php.

you have not used DATE_FORMAT in your query above.