If you always only store the date in your datetime fields and never store time, then you can keep between.

Just use ISO dates, e.g.

select * from myTable where DateTimeField between '20100101' and '20100501'

>Yes, I realize that 00 time is none-existing. It was just a typo; the actual value will have the time of 12:00:00. And it works, so far, in my tests. But I am thinking of changing the code in the 3P tool I am using and use only the date part.
>
>Thank you.
>
>>If datetime values stores with time part than it'll affect you. You do realize that
>>

>>TableName.FieldName between '2010-04-01 00:00:00' and '2010-05-01 00:00:00'
>>

will ignore all records for '2010-05-01' with none-zero time?
>>
>>
>>>Thank you for the explanation. I sort of have to use the BETWEEN since this is the syntax used in a 3P tool I am using. And since I am only concerned about comparing dates and not times, maybe the the fact that BETWEEN does not handle time part properly won't affect me. Or maybe I will take the time and change the 3P tool (I have the source code).
>>>