MySQL Date_Format not quite working

[tgrk35]

Ok, so here's my code:

$query2005 = "SELECT * FROM king_news WHERE DATE_FORMAT(news_date, '%Y') = 2005";
$result2005 = mysql_query($query2005);

$article = mysql_fetch_array($result2005);

I have the dates stored in the column 'news_date' in the 'king_news' table. Why is it that the query I have doesn't pull up anything? I have the dates stored as a PHP timestamp (seconds after dec. 31, 1969).

I use this same query (basically) in another page and it seems to work fine.

I think it has something to do with the date_format function in the sql query, however I probably don't know what I'm talking about.

Any help would be GREATLY appreciated!

Thanks,
Will :)

EDIT: BTW, here's the page address:
http://www.summel.net/archive.php

Thanks again!

[Nedals]

Try..
$query2005 = "SELECT * FROM king_news WHERE DATE_FORMAT(news_date, '%Y') = '2005'";

DATE_FORMAT returns a string.

[tgrk35]

Try..
$query2005 = "SELECT * FROM king_news WHERE DATE_FORMAT(news_date, '%Y') = '2005'";

DATE_FORMAT returns a string.

Still doesn't seem to work :'(.

Is it because the function is in the WHERE clause?

[tgrk35]

Ok, I tried this:

$year = "SELECT DATE_FORMAT(news_date, '%Y') news_date FROM king_news";
$year_result = mysql_query($year);

$line = mysql_fetch_array($year_result);

echo mysql_num_rows($year_result);

while ($line = mysql_fetch_array($year_result))
{
echo date("Y", $line['news_date']);
}

It seems to only bring up the null (dec 31 1969) entries as well.

Anyone have any ideas :(?

[tripwater]

Well I just plugged your query into my DB and changed the fields and it worked. THe only thing I did differently was name the field after like this

SELECT DATE_FORMAT(DateAssigned, '%Y') as Year FROM tasks;

so yours might look like

SELECT DATE_FORMAT(news_date, '%Y') as TempFieldName FROM king_news

[tgrk35]

AS is expendable. I tried it anyway though and it doesn't work :(.

I have the date stored as a timestamp I believe (seconds after 12-31-69).

I don't know what else to say, I'm not good at this stuff at all :(.

Thanks for your help :)

[NogDog]

Perhaps, since it's a timestamp column and not a date/datetime column:

$query2005 = "SELECT * FROM king_news WHERE FROM_UNIXTIME(news_date, '%Y') = 2005";

[tripwater]

yep that should do it. I completely missed that you were using a timestamp...

[Nedals]

try..
$year = "SELECT news_date, DATE_FORMAT(news_date, '%Y') FROM king_news";
and simply print out the two columns of timestamp and year.

If that does not work, the column or table names are in error or the problem lies elsewhere.

NOTE:
DATE_FORMAT in mySQL treats date, datetime, and timestamp the same way.
You don't need the FROM_UNIXTIME

[tripwater]

Well I just learned something new, thanks Nedals

[tgrk35]

Perhaps, since it's a timestamp column and not a date/datetime column:

$query2005 = "SELECT * FROM king_news WHERE FROM_UNIXTIME(news_date, '%Y') = 2005";


Ok, I did this and it worked!

Thanks for your help!