DATEFORMAT() problem :(

Shagrath

I'm having a couple of problems with the DATEFORMAT mysql function.

PostDate is TIMESTAMP(14)

I'm trying to show the date on screen in a format similar to:
'Tues 2 Dec 2003 - 11:30 am'
But i couldn't get it to work 🙁

				// Get Latest Post In Forum
				$var['temp']['latest_post'] = @mysql_query("SELECT * FROM $db_posts_table WHERE(ForumID = {$row['ForumID']}) ORDER BY PostDate DESC");
				$var['temp']['latest_post2'] = @mysql_num_rows($var['temp']['latest_post']);

			if($var['temp']['latest_post2'] == 0){
				$var['temp']['latest_post'] = "Currently No Posts";
			}
			else{
				$temp = mysql_fetch_array($var['temp']['latest_post']);
				$var['temp']['latest_post'] = $temp['PostDate'] . "<BR>By " . $temp['PostAuthor'];
			}

But the date kept getting displayed as '20040117152856'

I tried something similar to:

				// Get Latest Post In Forum
				$var['temp']['latest_post'] = @mysql_query("SELECT PostAuthor, DATEFORMAT('%a %b %Y - %h:%i',PostDate)  AS PostDate FROM $db_posts_table WHERE(ForumID = {$row['ForumID']}) ORDER BY PostDate DESC");
				$var['temp']['latest_post2'] = @mysql_num_rows($var['temp']['latest_post']);

			if($var['temp']['latest_post2'] == 0){
				$var['temp']['latest_post'] = "Currently No Posts";
			}
			else{
				$temp = mysql_fetch_array($var['temp']['latest_post']);
				$var['temp']['latest_post'] = $temp['PostDate'] . "<BR>By " . $temp['PostAuthor'];
			}

Any help would be appreciated 😃
Thanks

swr

I'm not that familiar with MySQL's date format stuff, but one thing that you are doing that looks a bit suspect is selecting "... as PostDate". You already have a field called PostDate, and now you're calling the result of the DATEFORMAT the same thing. So I think this gives you two things called PostDate, and you're getting the one you don't want. So change the "... as PostDate" to use a name that doesn't already exist.

I generally prefer to do formatting in PHP rather than the database.

Shagrath

bump 🙁

I tried that aswell, didnt do anything