Select statements using dates?

Databases
1

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?

2

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
3

Rudy you are a saint! thanks so much!

4

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?
5

SELECT COUNT(DISTINCT record_id) FROM …