Date Function

**rbutler** · May 25, 2004 07:03

I'm attempting to change mySQL's interpretion of the date from yyyy/mm/dd to dd/mm/yyyy. I found the DATE_FORMAT function for PHP, but I can't figure out the SQL syntax, the one I'll post below makes the date vanish, but the rest of the content is there.

Code:

$query="SELECT DATE_FORMAT(`Date`, '%d-%m-%Y'),`Title`, `Content` 
			FROM `CurrentNews` 
			ORDER BY `Date`";

Any suggestions?

**Helge** · May 25, 2004 07:35

Originally Posted by rbutler

Any suggestions?

Try using an alias.

PHP Code:


$query="SELECT DATE_FORMAT(`Date`, '%d-%m-%Y') as `formatteddate`

             , `Title`

             , `Content`

          FROM `CurrentNews`

      ORDER BY `Date`";

-Helge

**elgumbo** · May 25, 2004 07:37

You need to call your shiny new formatted date something

Code:

 $query="SELECT DATE_FORMAT(`DateField`, '%d-%m-%Y') AS 'NewDate'
 			FROM `CurrentNews` 
 			ORDER BY `Date`";

By the way, the DATE_Format command is part of MySQL not PHP. The PHP date functions are different again

>>edit>> HElge you beat me to it

**rbutler** · May 25, 2004 08:52

Yeah, creating an alias didn't work. I have this:

Code:

$query="SELECT DATE_FORMAT(Date, '%d-%m-%Y') AS 'NewDate', `Title`, `Content` 
			FROM `CurrentNews` 
			ORDER BY `Date`";

Everything but the date shows.

**Helge** · May 25, 2004 08:57

Did you try my code?
Also you need to access the alias (formatteddate) and not the column name (date) when echo'ing out the rows.

-Helge

**rbutler** · May 25, 2004 09:23

Yes, I tried you code, just substituted the alias for something I liked better. My code looks like this:

Code:

<?php 
//--  Declare connection to the database 
$db=mysql_connect ("localhost", "butler21_rbutler", "password") or die ('I cannot connect to the database because: ' . mysql_error()); 
//-- Select Database 
mysql_select_db ("butler21_news"); 
//--  Declare variable to pull data from table 
$query="SELECT DATE_FORMAT(Date, '%d-%m-%Y') AS 'NewDate', `Title`, `Content` 
			FROM `CurrentNews` 
			ORDER BY `Date`"; 
//--  Run the query on the DB 
$result = mysql_query($query); 
//--  Loop through the result array and display results 
while($row = mysql_fetch_array($result)) 
{ 
$date=$row['Date'];
$title=$row['Title'];
$content=$row['Content'];
//--  Display the results of the array
echo "<p>", $NewDate, "</p>", "\n" ,"<p>", $title, "</p>", "\n", "<p>", $content, "</p>";
} 
?>

I'm now accessing the NewDate in the echo'ing of rows.

**Helge** · May 25, 2004 09:35

In your while loop your are getting the Date row (This line: $date=$row['Date']; ).
That should be: $NewDate=$row['NewDate'];

Also you have single quotes around your alias (in the query). You should remove them or change to backticks. I used backticks in my code.

-Helge