sql BETWEEN

[Stan Busk]

Yes, in fact >= and <= works fine so I will better replace all the  
BETWEENs...

myDate >= '1/1/2006' AND myDate <= '1/31/2006' works.

myDate BETWEEN datetime '1/1/2006 00:00:00' AND datetime '1/31/2006  
23:59:59' doesnt' work.

stan

>> -----Original Message-----
>> From: valentina-bounces at lists.macserve.net
>> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of Stan Busk
>> Sent: Tuesday, February 13, 2007 2:29 PM
>> To: Valentina Developers
>> Subject: Re: sql BETWEEN
>>
>>
>> So if it is inclusive BETWEEN has a bug, doesn't it?
>>
> Hi Stan,
>
> Possibly not.  Most DBs I've worked with store the "date" as a
> "date-time".  When you pass it "01/31/06", you are really passing
> something like "01/31/06 00:00:00" so unless you have values in  
> your DB
> that are exactly at 12:00 midnight, you don't get anything for that
> date.  So, Between is technically inclusive but tough to use on dates.
> I would use "02/10/06" as you suggested yourself (though you might  
> get a
> record for that date if the time is exactly at midnight).   
> Personally, I
> prefer to use >='01/01/06' and <'02/01/06' as it is just safer.
>
> Take care.
>
> -John
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina