asked on

date format not working

i am trying to return the date in 'mm-dd-yyyy' from a mysql timstamp field 'date'. i have tried the attached code, but still displays the date in 'yyyy-mm-dd' format. any help would be great. thanks

$date = $_GET['date'];
$newdate = date("M d, Y", strtotime($date));
---------------------------------------------------
select statement
$sql = 'select service, activity, department, company, item, date, quantity, type, new'
	. ' from act '
	. ' order '
	. ' by company desc';
	$result = mysql_query($sql);
	return $result;
----------------------------------------------------------------------------------------------------------
insert statement
 
$query = 'INSERT INTO `supply` (`type`, `service`, `quantity`, `department`, `date`, `request`) VALUES (\''.$type.'\', \''.$service.'\', \''.$quantity.'\', \''.$department.'\', \''.$newdate.'\', \''.$request.'\');';
		mysql_query($query) or die(mysql_error());

Open in new window

waygood

If your displaying the result FROM the mysql table then use the following. Your use of GET and INSERT looks like your trying to insert the value as a timestamp, BUT I suggest inserting as a datetime to make it readable and prevent issues.

$sql = 'select service, activity, department, company, item, date, quantity, type, new'
      . ' from act '
      . ' order '
      . ' by company desc';
      $result = mysql_query($sql);
      return $result;

$row=mysql_fetch_assoc($result);
$date=date("M d, Y", $row['date']);

suggestion
$newdate = date("Y-m-d H:i:s", strtotime($date));

and you can use this to create a timestamp from the mysql datetime.

function timestamp_from_mysql($timestamp)
{
// $timestamp should be $mysql_datetime for easier reading.
      ereg ("([0-9]{2,4})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})", $timestamp, $regs);
      ereg ("([0-9]{2,4})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})", $timestamp, $regs);

      if (sizeof($regs)>1)
      {
            if (sizeof($regs)>4)
            {
                  $date=mktime($regs[4],$regs[5],$regs[6],$regs[2],$regs[3],$regs[1]);
            }
            else
            {
                  $date=mktime(0,0,0,$regs[2],$regs[3],$regs[1]);
            }
      }
      else
      {
            $date=0;
      }
      return $date;
}

Ray Paseur

From your code I can't see where you are displaying anything. But here is some information on dates that you should know about when you are doing programming that involves dates:
http://en.wikipedia.org/wiki/ISO_8601

If your date string is in ISO8601 format (YYYY-MM-DD HH:MM:SS, for example) you can do a lot of good things with it. You can sort it chronologically. You can use it in strtotime to get a timestamp and thence to reformat the date. You can use only part of the date string, omitting the trailing parts, etc. For internal representation, the ISO8601 is the way to go. Then you can reformat the date for external presentation.

$today = "2008-05-22 11:30";
$timestamp = strtotime($today);
$formatted_date = date("l, f J, m d Y", $timestamp); // or whatever date format you want to use.

HTH, ~Ray

ASKER CERTIFIED SOLUTION

hielo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peter_coop

ASKER

thanks