Format date in MySQL

Question

All,

I have a MYSQL table that has a column "DT_Created" defined with timestamp datatype. I would like to write a select query to retrieve the timestamp in the following fashion:

Sat, MM/DD/YYYY HH:MM:SS AM CST

Lengthy Explanation:
Day, Month/Date/4 Digit Year Hours:Minutes:Seconds AM or PM TimeZone

How can i do that?

Thanks

Answer 1

Use the DATE_FORMAT command (see the Reference Manual).

SELECT DATE_FORMAT(DT_Created, '%a, %c/%d/%Y %T %p') AS formatted FROM ...

The time zone is a bit more difficult... I'm not even sure in which timezone it will format the time.

Answer 2

retrieve it by using a query as such:

$sql = "SELECT UNIX_TIMESTAMP(DT_Created) as timeToFormat FROM table";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);

then you can format it with

$date = date("M/D/Y H:i:s",$row['timeToFormat']);

Answer 3

use the date_format function. This website will help you format the date http://www.mysqlformatdate.com