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';