[RESOLVED] Date formatting

[turtleton09]

Hope someone can help with this. I have a page that lists contacts based on particular criteria, and includes a date field.

Of the format :

ContactID, Contact, Employer, Telephone, Date to Contact

1, Jo Bloggs, Aardvark Airlines, 020 1234 5678, 2009-09-01
2, Jane Bloggs, Kangaroo Airlines, 020 8765 4321, 2009-10-01
etc

The query looks like :

mysql_select_db($database_connSearchTechUK, $connSearchTechUK);
$query_WADAEmployerContacts = "SELECT * FROM EmployerContacts LEFT JOIN Employers ON (EmployerContacts.EmployerID = Employers.EmployerID) WHERE DateToContact <= current_Date() AND DateToContact > 0000-00-00 AND CalledBack='N' ORDER BY DateToContact DESC";
$query_limit_WADAEmployerContacts = sprintf("%s LIMIT %d, %d", $query_WADAEmployerContacts, $startRow_WADAEmployerContacts, $maxRows_WADAEmployerContacts);
$WADAEmployerContacts = mysql_query($query_limit_WADAEmployerContacts, $connSearchTechUK) or die(mysql_error());
$row_WADAEmployerContacts = mysql_fetch_assoc($WADAEmployerContacts);

Can anyone advise how I can get the dates to display as 01 Sep 2009, 01 Oct 2009 etc?

I thought it should be a query along the lines of :

<?php
$colname_dateformat = "1"; 
if (isset($_GET['ContactID'])) { 
  $colname_dateformat = (get_magic_quotes_gpc()) ? $_GET['ContactID'] : addslashes($_GET['ContactID']); 
} 
mysql_select_db($database_connSearchTechUK, $connSearchTechUK); 
$query_dateformat = sprintf("SELECT date_format(DateToContact, '%%D %%b %%Y') as formatted_date FROM EmployerContacts WHERE ContactID = %s", $colname_dateformat);
$dateformat = mysql_query($query_dateformat, $connSearchTechUK) or die(mysql_error()); 
$row_dateformat = mysql_fetch_assoc($dateformat); 
$totalRows_dateformat = mysql_num_rows($dateformat);
?>

But that doesn't seem to work...

Thanks.

[dagon]

what's the "Date to Contact" field type?

[turtleton09]

Its a Date field type.

[turtleton09]

I'm trying to incorporate the date format into the main query now, but not sure of the syntax...

I've tried :

"SELECT ContactID, FirstName, LastName, Employer, Telephone, date_format(DateToContact, '%%D %%b %%Y') as formatted_date FROM EmployerContacts LEFT JOIN Employers ON (EmployerContacts.EmployerID = Employers.EmployerID) WHERE DateToContact <= current_Date() AND DateToContact > 0000-00-00 AND CalledBack='N' ORDER BY DateToContact DESC"

but with that

<?php echo($row_WADAEmployerContacts['formatted_date']); ?>

is outputting :

%D %b %Y

for each record.

Also tried :

"SELECT ContactID, FirstName, LastName, Employer, Telephone, date_format(DateToContact, '%%D %%b %%Y') FROM EmployerContacts etc"

and

<?php echo($row_WADAEmployerContacts['DateToContact']); ?>

but that's just drawing a blank again.

[Weedpacket]

Just a wild guess, based on conventional wisdom/common sense, but maybe it's supposed to be just %D, not %%D?

[turtleton09]

Go it - that should have been

date_format(DateToContact, '%D %b %Y')

in the first example above.

[turtleton09]

Weedpacket;10925310 wrote:

Maybe it's supposed to be just %D, not %%D?

Just spotted that and missed your post. I guess the odd thing is that where I've used that before on a details page, rather than a results page, it worked fine.