Page 1 of 1

Formatting a date from MySQL

Posted: Tue Oct 13, 2009 11:04 pm
by dkperez
I'm doing a query that retrieves a date from MySQL. In MySQL the date(s) are defined as datetime. Then I display it on an html form using php. My date comes up as
yyyy-mm-dd hh:mm:ss. I WANT it to display as mm/dd/yyyy. But, when I do the html as

<input name="InactivationDate" type="text" value="<?php echo date("m/d/Y",$InactivationDate); ?>" size="10" />

it displays as 12/31/1969...

Is php unable to properly format a MySQL datetime? If it is, what format do I need to use to get this to display correctly?

Re: Formatting a date from MySQL

Posted: Tue Oct 13, 2009 11:28 pm
by jegan.aaodis
Hi

Use explode function to get date , month and year and store in seperate values.
$dt="2009-11-12 16:10:30";
$dt_exp=explode(" ",$dt);
$date1=$dt_exp[0];
$time=$dt_exp[1];
$date1=explode("-",$date1);
$date1_y=$date1[0];//get the values year
$date1_m=$date1[1];//get the values month
$date1_d=$date1[2];//get the values date

$enddt=$date1_m."/".$date1_d."/".$date1_y." ".$time;

<input name="InactivationDate" type="text" value="<?php echo $enddt; ?>" size="10" />

Thanks

Re: Formatting a date from MySQL

Posted: Wed Oct 14, 2009 12:43 am
by Mirge
dkperez wrote:I'm doing a query that retrieves a date from MySQL. In MySQL the date(s) are defined as datetime. Then I display it on an html form using php. My date comes up as
yyyy-mm-dd hh:mm:ss. I WANT it to display as mm/dd/yyyy. But, when I do the html as

<input name="InactivationDate" type="text" value="<?php echo date("m/d/Y",$InactivationDate); ?>" size="10" />

it displays as 12/31/1969...

Is php unable to properly format a MySQL datetime? If it is, what format do I need to use to get this to display correctly?
Try:

<?php echo date("m/d/Y", strtotime($InactivationDAte)); ?>

http://www.php.net/date/

The 2nd argument is a timestamp, not a string with the date... so we use strtotime() to generate a timestamp based on the formatted date that came from MySQL.

Re: Formatting a date from MySQL

Posted: Wed Oct 14, 2009 1:25 am
by Christopher
It is probably easiest to let MySQL do the work for you.

http://dev.mysql.com/doc/refman/5.0/en/ ... ate-format

So your query becomes something like: SELECT DATE_FORMAT(mydate, '%c/%e/%Y') AS formatted_date FROM mytable;

Re: Formatting a date from MySQL

Posted: Wed Oct 14, 2009 10:10 am
by dkperez
Thanks for all the help guys...

I'll have to go learn about explode......

I think I'll let mysql do the work for queries........

This particular form is intended to be used as a query form as well as a display form, so I may run into problems
getting the daste information from the form for doing the mysql query, but I'll cross that bridge when I come
to it......