Formatting a date from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dkperez
Forum Commoner
Posts: 26
Joined: Fri Jun 26, 2009 9:41 am

Formatting a date from MySQL

Post 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?
jegan.aaodis
Forum Newbie
Posts: 15
Joined: Fri Oct 09, 2009 1:56 am

Re: Formatting a date from MySQL

Post 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
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: Formatting a date from MySQL

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Formatting a date from MySQL

Post 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;
(#10850)
dkperez
Forum Commoner
Posts: 26
Joined: Fri Jun 26, 2009 9:41 am

Re: Formatting a date from MySQL

Post 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......
Post Reply