how to format date coming from MYSQL

Question

Hi

Simple question but i need it :)

  $query = "SELECT * 
FROM  `set` 
WHERE ID = '$id'";

$result = mysql_query($query);
$info = mysql_fetch_assoc($result);

$frmdate = $info['date'];

So far i tried

$timestamp = strtotime($info['date']);
$joined_date = date("j M. Y", $timestamp);

but no luck !

"date" field in mysql is DATE data type

I want to make it like "23 Oct. 2009"

how can i do it ?

Thanks

Answer 1

You can use strtotime to convert the string representation of the date into a timestamp then you can use date to convert the timestamp into whatever format you like:

$timestamp = strtotime($frmdate);
$formatted_date = date("j M. Y", $timestamp);

You can also move the translation from the yyyy-mm-dd format to timestamp into the query itself, using:

select unix_timestamp(field_name) as field_name_timestamp

Answer 2

You could format the date in the MySQL database server.

SELECT  *, DATE_FORMAT( date, '%e %b. %Y' ) AS formatted_date
FROM    `set` 
WHERE   ID = '$id'

Then you would just need to modify your client php code to just use the string returned by the database call in $info['formatted_date']. Use 'd' in place of 'e' in the format string if you do not want a leading zero on the day of month.

Answer 3

you can format the date using the date_format mysql function. This website should help you http://www.mysqlformatdate.com