Hi, All when I insert the date in MySQL the format is 2009/07/13 and when I use SELELCT query it shows in the same format 2009/07/13. But I need to show in the Format 13-Jul-2009.

$result = mysql_query("SELECT Date FROM leaseentry WHERE SiteId REGEXP '$a'");

Please guide me.

Please Reply and give me solution how I can show the Date in Aboove mentioned format.

Look into the date function.

Please search for a solution before create a thread.

<? $result = mysql_query("SELECT Date FROM leaseentry WHERE SiteId REGEXP '$a'");
$ss=mysql_fetch_assoc($result);
 echo $date=date("F j, Y, g:i a",strtotime($ss['Date']));
?>

hope this may work....

Member Avatar for diafol

SQL has its own way of dealing with dates and various formats - do not use php functions- although praveen's version will work

$q = "SELECT *, DATE_FORMAT(yourdatefieldname,'%e-%b-%Y') AS mydate FROM yourtable";
$r = mysql_query($q);
$d = mysql_fetch_array($r);

echo $d['mydate'];

This will return all your fields as well as the custom mydate. See the MySQL manual at: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
I think this'll work - you may need to escape the '-', although I don't think so.

This is my Code and I want to show Lease agreement start date , end date , PO strart date and PO end date.
please guide me. Praveen ur code is working but when i try to merge in this it shows 1-jan-1970.

<?php
$con = mysql_connect("localhost","root","root");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$a= $_POST["a"];
mysql_select_db("onm", $con);

$result = mysql_query("SELECT * FROM leaseentry WHERE SiteId REGEXP '$a'");

mysql_select_db("new", $con);



while($row = mysql_fetch_array($result))
  {
  echo "<table cellpadding=2 cellspacing=2 width=100%>
<tr>


</tr>";
  echo "<tr>";
  echo "<th bgcolor=#FFCC00 >SiteID</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['SiteId'] . "</td>";
    echo "</tr>";
  echo "<tr>";
						
  echo "<th bgcolor=#FFCC00>Owner Name</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['OwnerName'] . "</td>";
  
    echo "</tr>";
	
	echo "<tr>";
	echo "<th bgcolor=#FFCC00>Lease Agreement</th>";
	 echo "<td bgcolor=#9BCB5D><a href=".$row['LA'].">".$row['ch']."</a></td>";
	  

	  echo "</tr>";
	  
	
	
	echo "<tr>";
  echo "<th bgcolor=#FFCC00>LA Start Date</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['LAPeriodStart'] . "</td>";
    echo "</tr>";
	
	echo "<tr>";
  echo "<th bgcolor=#FFCC00>LA End Date</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['LAPeriodEnd'] . "</td>";
    echo "</tr>";
	
	  echo "<tr>";
	echo "<th bgcolor=#FFCC00>Purchase Order</th>";
	 echo "<td bgcolor=#CB9B5D><a href=".$row['PO'].">".$row['ch']."</a></td>";

	  echo "</tr>";
	  
	
	 echo "<tr>";
  echo "<th bgcolor=#FFCC00>PO Start Date/th>";
  echo "<td bgcolor=#FEE9A9>" . $row['POStartDate'] . "</td>";
    echo "</tr>";
	
	 echo "<tr>";
  echo "<th bgcolor=#FFCC00>PO End Date</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['POEndDate'] . "</td>";
    echo "</tr>";
	

	  echo "<tr>";
	echo "<th bgcolor=#FFCC00>SPA</th>";
	 echo "<td bgcolor=#CB9B5D><a href=".$row['SPA'].">".$row['SPA']."</a></td>";

	  echo "</tr>";
	
	 $b =$row['SiteId']; 
  }
echo "</table>";


mysql_close($con);

?>

i dont understand u said my code is working and its is displaying as what u wanted then what is your requirement?
if you want to integrate my code then

<?php
$con = mysql_connect("localhost","root","root");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$a= $_POST["a"];
mysql_select_db("onm", $con);

$result = mysql_query("SELECT * FROM leaseentry WHERE SiteId REGEXP '$a'");

mysql_select_db("new", $con);




while($row = mysql_fetch_array($result))
  {
  echo "<table cellpadding=2 cellspacing=2 width=100%>
<tr>


</tr>";
  echo "<tr>";
  echo "<th bgcolor=#FFCC00 >SiteID</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['SiteId'] . "</td>";
    echo "</tr>";
  echo "<tr>";
						
  echo "<th bgcolor=#FFCC00>Owner Name</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['OwnerName'] . "</td>";
  
    echo "</tr>";
	
	echo "<tr>";
	echo "<th bgcolor=#FFCC00>Lease Agreement</th>";
	 echo "<td bgcolor=#9BCB5D><a href=".$row['LA'].">".$row['ch']."</a></td>";
	  

	  echo "</tr>";
	  
	
	
	echo "<tr>";
  echo "<th bgcolor=#FFCC00>LA Start Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("F j, Y, g:i a",strtotime($row['LAPeriodStart'])). "</td>";
    echo "</tr>";
	
	echo "<tr>";
  echo "<th bgcolor=#FFCC00>LA End Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("F j, Y, g:i a",strtotime($row['LAPeriodEnd'])). "</td>";
    echo "</tr>";
	
	  echo "<tr>";
	echo "<th bgcolor=#FFCC00>Purchase Order</th>";
	 echo "<td bgcolor=#CB9B5D><a href=".$row['PO'].">".$row['ch']."</a></td>";

	  echo "</tr>";
	  
	
	 echo "<tr>";
  echo "<th bgcolor=#FFCC00>PO Start Date/th>";
  echo "<td bgcolor=#FEE9A9>" .date("F j, Y, g:i a",strtotime($row['POStartDate'])). "</td>";
    echo "</tr>";
	
	 echo "<tr>";
  echo "<th bgcolor=#FFCC00>PO End Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("F j, Y, g:i a",strtotime($row['POEndDate']))  . "</td>";
    echo "</tr>";
	

	  echo "<tr>";
	echo "<th bgcolor=#FFCC00>SPA</th>";
	 echo "<td bgcolor=#CB9B5D><a href=".$row['SPA'].">".$row['SPA']."</a></td>";

	  echo "</tr>";
	
	 $b =$row['SiteId']; 
  }
echo "</table>";


mysql_close($con);

?>

if i am wrong please post ur requirement...

Thanks . Its working.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.