/    Sign up×
Community /Pin to ProfileBookmark

MySQL Date_Format not quite working

Copy linkTweet thisAlerts:
Jul 05.2005

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:
[url]http://www.summel.net/archive.php[/url]

Thanks again!

to post a comment
PHP

10 Comments(s) ↴

Copy linkTweet thisAlerts:
@NedalsJul 05.2005 — Try..

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

DATE_FORMAT returns a string.
Copy linkTweet thisAlerts:
@tgrk35authorJul 05.2005 — Try..

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

DATE_FORMAT returns a string.[/QUOTE]

Still doesn't seem to work ?.

Is it because the function is in the WHERE clause?
Copy linkTweet thisAlerts:
@tgrk35authorJul 05.2005 — 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 ??
Copy linkTweet thisAlerts:
@tripwaterJul 05.2005 — 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
Copy linkTweet thisAlerts:
@tgrk35authorJul 05.2005 — 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 ?
Copy linkTweet thisAlerts:
@NogDogJul 05.2005 — Perhaps, since it's a timestamp column and not a date/datetime column:
[code=php]
$query2005 = "SELECT * FROM king_news WHERE FROM_UNIXTIME(news_date, '%Y') = 2005";
[/code]
Copy linkTweet thisAlerts:
@tripwaterJul 05.2005 — yep that should do it. I completely missed that you were using a timestamp...
Copy linkTweet thisAlerts:
@NedalsJul 05.2005 — 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
Copy linkTweet thisAlerts:
@tripwaterJul 05.2005 — Well I just learned something new, thanks Nedals
Copy linkTweet thisAlerts:
@tgrk35authorJul 05.2005 — Perhaps, since it's a timestamp column and not a date/datetime column:
[code=php]
$query2005 = "SELECT * FROM king_news WHERE FROM_UNIXTIME(news_date, '%Y') = 2005";
[/code][/QUOTE]

Ok, I did this and it worked!

Thanks for your help!
×

Success!

Help @tgrk35 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.17,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @nearjob,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,
)...