What DB's is it *NOT* true in, Doug? And are you saying there are versions of SQL Server where it is not true?
I don't see how a DB could be ANSI-compliant and not have that situation exist. No matter how the DATETIME value is stored. (Of course, if the *USER* chooses to store the date and time in separate fields, that's a completely different issue/question/solution. But doing that creates its own complexities and I usually recommend against it.)
Anyway, all I wrote is also true in Access (or any JET-accessed DB or pseudo-db, such as Excel or text files).
And of course another way to solve the problem is to convert the date-and-time value to date-only as part of the BETWEEN:
... WHERE DATEVALUE(DateColumn) BETWEEN '1/1/2009' AND '1/31/2009'
... WHERE DATE(DateColumn) BETWEEN '1/1/2009' AND '1/31/2009'
... WHERE CONVERT(DATETIME, CONVERT(VARCHAR, DateColumn, 112), 112) BETWEEN '1/1/2009' AND '1/31/2009'
Only SQL Server is too stupid to give us a nice handy function to do this (and similarly omits other handy datetime functions).
Anyway...You do *NOT* want to use a function like that if the field is indexed, because you just caused a full scan of all values in the index. If you use the >= and < solution that I showed, you can take full advantage of an index on the column.