Mysql Select Problem 1

[PaulinKC]

Hello, I've got a little problem with a select statement using Mysql.

I have a table named tblNews where I have a field named newsDate which stores the date the news article was created. I've used the following sql select statement to get all records where the month is the same as the current month.

SELECT * from tblNEWS WHERE extract(MONTH from newsDate) = extract(MONTH from current_date) Order By newsDate DESC

Works fine, returns all rows where the current_month is = to the month in newsDate. However, I don't want it to return all rows for that month in previous years, only for the current year.

I tried the following select statement to achieve this:

SELECT * from tblNEWS WHERE extract(MONTH from newsDate) = extract(MONTH from current_date) AND extract(YEAR from newsDate) = extract(YEAR from current_date) Order By newsDate DESC

That select statement returns no rows at all. Am I missing something here?

 

[abombss]

I think it should work, but I couldn't get your query to work either. Instead try using the

extract

function with

YEAR_MONTH

instead of using the

AND

in your where clause.

select * from tblNews where extract(YEAR_MONTH from newsDate) = extract(YEAR_MONTH from CURRENT_DATE);
+--------+--------------+------------+
| newsId | title        | newsDate   |
+--------+--------------+------------+
|      5 | Story 1 2003 | 2003-07-10 |
|      6 | Story 2 2003 | 2003-07-12 |
+--------+--------------+------------+
2 rows in set (0.00 sec)

I will look into this, but it should work your way too. Maybe its bug because at first glance it is returning what it should.

mysql> select extract(YEAR from newsdate) as "nYr", extract(MONTH from newsdate ) as "nMon", extract(YEAR from current_date) as "cYr", extract(MONTH from current_date) as "cMon" from tblNews;
+------+------+------+------+
| nYr  | nMon | cYr  | cMon |
+------+------+------+------+
| 2002 |    7 | 2003 |    7 |
| 2002 |    7 | 2003 |    7 |
| 2002 |    7 | 2003 |    7 |
| 2002 |    7 | 2003 |    7 |
| 2003 |    7 | 2003 |    7 |
| 2003 |    7 | 2003 |    7 |
+------+------+------+------+
6 rows in set (0.00 sec)

abombss

 

[PaulinKC]

Thanks for your help! Worked like a charm!!

 

[abombss]

It looks like this was a bug that has been fixed. You may need to patch your server or use the workarounds. Here is the link to the bug.

http://bugs.mysql.com/bug.php?id=614

abombss