sql BETWEEN

[Stan Busk]

Hi,

Ok, right now I am using:

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

It returns nothing despite it should return one record.

However:

myDate >= '1/1/2006' AND myDate <= '1/31/2006' works as expected!

stan

> Hi Stan,
>
> just as an idea
>
> couldn't it be that when you pass '01/31/06' it actually means  
> '01/31/06:00:00:00' (--> complete timestamp).
> If this is the case then you exclude all values as '01/31/06:01:00:00'
>
> You wouldn't be the first walking in that one
>
> hth
>
> Bart Pietercil
>
>
> On 13-feb-07, at 20:29, Stan Busk wrote:
>
>>
>> So if it is inclusive BETWEEN has a bug, doesn't it?
>>
>>> On 13/2/07 9:15 PM, "Stan Busk" <maxprog at mac.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> I don't remember (and can't find in the doc), is BETWEEN inclusive?
>>>
>>> yes
>>>
>>>> In a query I have:
>>>>
>>>> myDate BETWEEN '01/01/06' AND '01/31/06'
>>>>
>>>> It returns everything between 01/01 and 01/30 rather than 01/01 and
>>>> 01/31 ?
>>>>
>>>> Should I modify my query to
>>>>
>>>> myDate BETWEEN '01/01/06' AND '02/01/06' ?
>>>
>>> -- 
>>> Best regards,
>>>
>>> Ruslan Zasukhin
>>> VP Engineering and New Technology
>>> Paradigma Software, Inc
>>>
>>> Valentina - Joining Worlds of Information
>>> http://www.paradigmasoft.com
>>>
>>> [I feel the need: the need for speed]
>>>
>>>
>>> _______________________________________________
>>> Valentina mailing list
>>> Valentina at lists.macserve.net
>>> http://lists.macserve.net/mailman/listinfo/valentina
>>
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina