How to Count records between two datetimes?

Experts,

I have a table with two columns, id (varchar) and dtcol (datetime).

I'd like to count the number of rows between two dates.  I've tried this as follows:

SELECT COUNT(id) FROM Table WHERE `dtcol` BETWEEN DATETIME '2008-10-10 11:45:00' AND '2008-10-10 10:45:00';

This gives me a syntax error.  Please help me with the correct syntax!
mhouldridgeAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>.. is returning all values greater than '2008-10-10 10:45:00', and all values less than '2008-10-10 11:45:00', which is not what I want.

which is exactly what the BETWEEN is doing.
the AND will ensure that BOTH conditions must be true.

what you "fear" is what would result from this query:


SELECT COUNT(id) 
FROM Table  
WHERE ( `dtcol` >= '2008-10-10 10:45:00' OR `dtcol` <= '2008-10-10 11:45:00' )

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
SELECT COUNT(id) FROM Table WHERE `dtcol` BETWEEN '2008-10-10 11:45:00' AND '2008-10-10 10:45:00';

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: as the second time is before the first, you might get no records at all, actually...
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
mhouldridgeAuthor Commented:
IS this correct:


SELECT COUNT(id) FROM Table  WHERE `dtcol` >= '2008-10-10 10:45:00' AND `dtcol` <= '2008-10-10 11:45:00'

Returns a result now.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
syntax is correct.
if it's semantically correct depends on what exactly you need to return.
I must guess that probably, you want to exclude the last value, so that running the query for the next "hour" will not return the same record again, if that was on exactly that time.

SELECT COUNT(id) FROM Table  WHERE `dtcol` >= '2008-10-10 10:45:00' AND `dtcol` < '2008-10-10 11:45:00'

Open in new window

0
 
mhouldridgeAuthor Commented:
angelIII, no result was returned for your query - I've also added the first time before that last and this doesnt work.

I believe my current attempt is the correct method, although Im concerned that this isnt getting the rows between times..

0
 
mhouldridgeAuthor Commented:
I'd like to get rows between two dates... seems pretty straight forward to me, although I can't get the BETWEEN TO WORK.

I'm concerned that my query...

SELECT COUNT(id) FROM Table  WHERE `dtcol` >= '2008-10-10 10:45:00' AND `dtcol` <= '2008-10-10 11:45:00'

.. is returning all values greater than '2008-10-10 10:45:00', and all values less than '2008-10-10 11:45:00', which is not what I want.
0
 
mhouldridgeAuthor Commented:
Yep, that's answered my question.

It's been a while since I've done any MySQL - Apologies for being obtuse!, and thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.