Select statements using dates?

[wickedneat]

I have a table that contains a table with the following column

date datetime 0000-00-00 00:00:00

I need to run the following queries, however, I'm unsure about what to point in the where portion of the statement.

SELECT COUNT(*) FROM table where date = today's date;
SELECT COUNT(*) FROM table where date = this month
SELECT COUNT(*) FROM table where date = last month


I'm unsure how to connect today's date, month, and last month to the format 0000-00-00 00:00:00 to get a count.

Any suggestions?

[r937]

today

WHERE `date` >= CURRENT_DATE
  AND `date`  < CURRENT_DATE + INTERVAL 1 DAY

this month

WHERE `date` >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
  AND `date`  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                             + INTERVAL 1 MONTH

last month

WHERE `date` >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                             - INTERVAL 1 MONTH
  AND `date`  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY

[wickedneat]

Rudy you are a saint! thanks so much!

[wickedneat]

These queries worked perfectly. Thanks again!

Just a quick question

Lets say that I added another column name: record_id

In this column I could have records such as

abc
xyz
def
abc

Sometimes the records will be duplicate.

However, I want the count to ignore the duplicate records of that column.

So I'm thinking its something like this but unsure of the syntax:

WHERE `date` >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                             - INTERVAL 1 MONTH
  AND `date`  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
  AND NOT 'record_id' = duplicate?

[r937]

SELECT COUNT(DISTINCT record_id) FROM ...