Using Between on only time portion of dates

I have the following q开发者_运维知识库uery:

SELECT COUNT(*) FROM leads WHERE create_date > '06:00' AND create_date < '18:00';
SELECT COUNT(*) FROM leads WHERE create_date > '06:00' AND create_date < '18:00';

However, the create_date column looks like '2011-08-26 10:18:01' and i want to find everything between just those time periods, regardless of the day.

How can I just query the time from the create_date values?

How would also find all values not within those time periods? NOT BETWEEN ....

---

To get the time from the date, you can use the TIME() function.

SELECT COUNT(*) FROM leads WHERE TIME(create_date) > '06:00' AND create_date < '18:00';
SELECT COUNT(*) FROM leads WHERE TIME(create_date) > '06:00' AND create_date < '18:00';

To find times outside this range, just invert everything in your where clauses.

SELECT COUNT(*) FROM leads WHERE TIME(create_date) < '06:00' AND create_date > '18:00';
SELECT COUNT(*) FROM leads WHERE TIME(create_date) < '06:00' AND create_date > '18:00';

---

Assuming that create_date is of type DATETIME, to extract the time part only use TIME(create_date).

And yes, NOT BETWEEN will get the inverse set.

---

You can just cast to a TIME:

SELECT COUNT(*) FROM leads WHERE TIME(create_date) > '06:00' AND TIME(create_date) < '18:00';

---

Use the TIME() function to return only the time portion of a DATETIME column.

SELECT COUNT(*) FROM leads WHERE TIME(create_date) > '06:00' AND TIME(create_date) < '18:00';

To find the values outside these time periods, use

SELECT COUNT(*) FROM leads WHERE TIME(create_date) <= '06:00' AND TIME(create_date) >= '18:00';