Am I using convert() wrong?

[nicolanicola]

I am extremely new to this, so please bear with me if I don't make sense.

 

In a php script, I add a datestamp to a field by using the now() function.

 

I am retrieving this field now from db, I have successfuly managed to get the date and convert it - select DATE_FORMAT(date,'%d-%m-%Y') as postedDate.

 

But I can't get the time at all. Can someone please help? I've tried using convert but I think I'm doing it wrong, because my php code just exits and the html then continues, after the following select statement.  If I take convert out it works, so I figure something is wrong with the convert statement.

 

mysql_query("SELECT asset_id, name, email, website ,comment, CONVERT(varchar(10), date, 108) as theTime, DATE_FORMAT(date,'%d-%m-%Y') as postedDate FROM Comment WHERE asset_id='lbi-type' ORDER BY date", $connection);

[spfoonnewb]

Date and time are combined in a datetime field.

 

mysql_query("SELECT asset_id, name, email, website ,comment, DATE_FORMAT(date,'%h:%i:%s') as theTime, DATE_FORMAT(date,'%d-%m-%Y') as postedDate FROM Comment WHERE asset_id='lbi-type' ORDER BY date", $connection);

[nicolanicola]

Thank you, you've just saved me hours of work

 

Spent about an hour trying to get convert to work.

[nicolanicola]

Incidentally, my order by clause isn't working.  My comments are displayed from oldest to newest.  Am I using it correctly?

[andrewgauger]

"order by date desc" to reverse order.  http://dev.mysql.com/doc/refman/5.0/en/select.html

[nicolanicola]

thanks