Can't quite figure out DATE_FORMAT()

[mcpalmer]

Hi,

Got a problem with date_format() , using this query:

SELECT name, description, pic, download, DATE_FORMAT(date_added,'%D %M %Y'), dl_num FROM database ORDER BY download DESC

Where date_added is database value. I obviously want to format date as i select it. What am i doing wrong? Looked in the manual and this looked to be the way but my syntax is wrong somewhere. Do i have to create a separate select statement for date format or something? Would it be better to format the date after the date data has been selected?

cheers

[chazzy]

what is the data type for the "date_added" column. it should be a date or datetime type.

[mcpalmer]

yes, its date. I think i might have to use 'as' somewhere - as Date at the end maybe? i'll try that. I've looked through a number of ebooks on sql and there is surprisingly little about the date_format() function. Am I missing a simpler way to set the date to the correct british format of day, month, year ?

[NogDog]

I don't see anything obviously wrong with the query. What are the symptoms that tell you it's not working (an error message, no rows returned, etc.)?

[mcpalmer]

There is no error given, it 'works', but the date output as expressed in date_format is ignored - the date output is still the default YYYY MM DD when results are displayed. I'm using PHP to display the results btw

[mcpalmer]

Oh, and also using echo command to display data, if this is relevant

[chazzy]

what happens if you just echo the SQL? does it show the command properly?

[NogDog]

I don't know if it does/should make a difference, but I always do

DATE_FORMAT(date_col, 'format string') AS date_formatted

, then reference the 'date_formatted' element of each result row.

[mcpalmer]

Would I need to create a new date_formatted column in the table then?

[NogDog]

Would I need to create a new date_formatted column in the table then?

No. It just means that any further reference to that formatted output within the query or, proably more importantly, within the script where you're running the query would be by that name. For instance, in a PHP script where you might be referring to that column in a result row, you would refer to it as $row['date_formatted'] instead of by the column name in the table.

[mcpalmer]

Nope, it doesn't work, i reference $row['date_formatted'] and the table column for the data is blank. Could there be a problem with my php or sql settings?

[NogDog]

Nope, it doesn't work, i reference $row['date_formatted'] and the table column for the data is blank. Could there be a problem with my php or sql settings?

Yes.

Show us the PHP if you want.

[mcpalmer]

Here it is:

mysql_select_db($database_ss_list, $ss_list);

if(!reorder){
$query_Recordset1 = "SELECT name, description, pic, download, DATE_FORMAT(date_added,'%D %M %Y') AS date_formatted, dl_num FROM screensavers ORDER BY download DESC";

} else {
	$query_Recordset1 = "SELECT name, description, pic, download, date_added, dl_num FROM screensavers ORDER BY '$reorder' DESC";
}

$unformatted_date = $row_Recordset1['date_added'];
$Recordset1 = mysql_query($query_Recordset1, $ss_list) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

and then i try to call data in the html:

<?php echo $row_Recordset1['date_formatted']; ?>

all other calls to query data work fine, as does date_added when i call it. The only problem is the date_formatted data above. Any ideas?

[chazzy]

if date_added works, but date_formatted isn't, I'm thinking it's more to do with your logic. date_added should not work in the if(!reorder) part of the condition. You might want to check the logic within the page to see which sql is getting processed.

[mcpalmer]

Of course, you are right! Left the dollar sign off $reorder variable so it was automatically running the else code, cheers it works now, thanks for the help