MySQL: show only dates within the month of March

Question

I am trying to show only the dates within the month of March and can't quite figure it out.

Here is the table...

Here is my current code...

SELECT billing.charged_datetime-- SUM(billing.amount) AS total_revenue
FROM lead_gen_business.billing;
WHERE billing.charged_datetime BETWEEN '2012-03-01' AND '2012-03-30';

What am I doing wrong?

Answer 1

You are comparing TIMESTAMP or DATETIME thus you can't just go along with date in the comparison, you also need to provide the time.

Note that before the comparison, the type is converted to match the column type. Have a look into this query for an illustration:

SELECT CAST('2015-10-18' AS DATETIME)

So one option is to use the condition as follows (assuming no decimals in the data type):

WHERE billing.charged_datetime BETWEEN '2012-03-01' AND '2012-03-30 23:59:59'

The best (readable) option is to however change the condition into:

WHERE MONTH(charged_datetime) = 3 AND YEAR(charged_datetime) = 2012

https://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

Edit after seeing your comments on other answers. Is this what you were looking for?

SELECT DATE(billing.charged_datetime)
FROM lead_gen_business.billing
WHERE MONTH(billing.charged_datetime) = 3 AND YEAR(billing.charged_datetime) = 2012;

Answer 2

Since you enter Datetime in charged_datetime. You would need to use the convert function of mysql.

Use this :-

SELECT billing.charged_datetime-- SUM(billing.amount) AS total_revenue
FROM lead_gen_business.billing;
WHERE billing.charged_datetime BETWEEN CONVERT(datetime,'2012-03-01)' AND CONVERT(datetime,'2012-03-30');