How can I format datetime?

[mr_james]

I have an automatic datetime field which returns a value as something like this: "20030120110110"

But I want it to say "20.01.2003 at 10:11".

The current SQL I use goes $sql= "SELECT event.username, event.id, event.autodate, event.title, event.message, users.firstname, users.lastname FROM users INNER JOIN event ON users.username = event.username;"

-where autodate is the datetime field.

How can I return that data in the required format, or post-format it?

Thanks! James.

[ultraslacker]

Both mysql and php have formatting functions for datetime. Read up on date_format in the mysql manual.

[mr_james]

I have tried putting it into a date type:
$d = date($result["autodate"]) but I get something completly different! I was going to use this method to then pick out the "D" and "Y" etc, but I just need help knowing which methods and how to format it.

[ultraslacker]

DATE_FORMAT is explained in detail in the mysql manual. You do not need to cast the value as it is a timestamp.

SELECT DATE_FORMAT(datetime_field, '%d.%m.%Y at %H.%i')