[RESOLVED] mySQL date conversion synytax

turtleton09

This is something I've been meaning to figure out once and for all.

I have a field in a mySQL table 'Date_Added', of timestamp format, and is set to add in athe current time when a new record is added.

This natively outputs as

2006-12-06 08:35:15

So I'm trying to convert it to either 06-12-2006 or 6th December 2006.

My current query looks like

"SELECT * FROM Keywords INNER JOIN (Photos INNER JOIN PhotoKeywords ON Photos.Photo_ID = PhotoKeywords.Photo_ID) ON Keywords.Keyword_ID = PhotoKeywords.Keyword_ID WHERE Photos.Photo_ID = %s 
ORDER BY Keyword ASC"

Where Date_Added is a field in the table 'Photos'

So I'm trying to add in something like :

date_format(Date_Added, '%a %D %b %Y') as formatted_date from Photos;

to the query

and change the echo from

<?php echo $row_PhotoSimple['Date_Added']; ?>

<?php echo ['formatted_date']; ?>

or similar.

I've tried just about every permutation with these bits of codes, but can't quite seem to get the syntax correct.

Can anyone let me know where I'm going wrong?

rincewind456

This will get you an output like 6th December 2006

DATE_FORMAT(date_added , '%D %M %Y' ) AS formatted_date

This would give you something like 06-12-2006

DATE_FORMAT(date_added , '%d-%c-%Y' ) AS formatted_date

this would then output like

<?php echo $row['formatted_date']; ?>

turtleton09

rincewind456 wrote:
This will get you an output like 6th December 2006
DATE_FORMAT(date_added , '%D %M %Y' ) AS formatted_date
This would give you something like 06-12-2006
DATE_FORMAT(date_added , '%d-%c-%Y' ) AS formatted_date
this would then output like
<?php echo $row['formatted_date']; ?>

Cheers - I think the reason it didn't work originally was because I had the syntax in the query wrong, but it was fine when I created a separate query for it.

turtleton09

Actually, the date isn't working I've noticed.

I added a sample batch of records on 23 Nov, and they all display 23rd Nov 2006 quite happily.

But when I add in any new records, they're also displaying as 23rd Nov 2006, but are showing with today's date in mySQL.

My code is :

<?php require_once('Connections/Photolibrary.php'); ?>
<?php
$colname_PhotoSimple = "1";
if (isset($_GET['Photo_ID'])) {
  $colname_PhotoSimple = (get_magic_quotes_gpc()) ? $_GET['Photo_ID'] : addslashes($_GET['Photo_ID']);
}
mysql_select_db($database_Photolibrary, $Photolibrary);
$query_PhotoSimple = sprintf("SELECT * FROM Photos WHERE Photo_ID = %s", $colname_PhotoSimple);
$PhotoSimple = mysql_query($query_PhotoSimple, $Photolibrary) or die(mysql_error());
$row_PhotoSimple = mysql_fetch_assoc($PhotoSimple);
$totalRows_PhotoSimple = mysql_num_rows($PhotoSimple);

$colname_PhotoComplex = "1";
if (isset($_GET['Photo_ID'])) {
  $colname_PhotoComplex = (get_magic_quotes_gpc()) ? $_GET['Photo_ID'] : addslashes($_GET['Photo_ID']);
}
mysql_select_db($database_Photolibrary, $Photolibrary);
$query_PhotoComplex = sprintf("SELECT * FROM Keywords INNER JOIN (Photos INNER JOIN PhotoKeywords ON Photos.Photo_ID = PhotoKeywords.Photo_ID) ON Keywords.Keyword_ID = PhotoKeywords.Keyword_ID WHERE Photos.Photo_ID = %s ORDER BY Keyword ASC", $colname_PhotoComplex);
$PhotoComplex = mysql_query($query_PhotoComplex, $Photolibrary) or die(mysql_error());
$row_PhotoComplex = mysql_fetch_assoc($PhotoComplex);
$totalRows_PhotoComplex = mysql_num_rows($PhotoComplex);

mysql_select_db($database_Photolibrary, $Photolibrary);
$query_DateFormat = "SELECT date_format(Date_Added, '%D %b %Y') as formatted_date FROM photos";
$DateFormat = mysql_query($query_DateFormat, $Photolibrary) or die(mysql_error());
$row_DateFormat = mysql_fetch_assoc($DateFormat);
$totalRows_DateFormat = mysql_num_rows($DateFormat);
?>

and

<?php echo $row_DateFormat['formatted_date']; ?>

To display the date.

Any ideas what's happening here?