|
|
Posted by Roy Harvey on 05/04/06 00:16
My assumption is that the tradedate is a datetime datatype that
includes a time, while the begin and end dates are just dates. The
comparison is will be done on datetimes, which include both date and
time.
>WHERE tradedate BETWEEN begindate AND enddate
Consider what this can look like when we substitute actual values.
WHERE
'2006-05-03 17:12:51.420' BETWEEN
'2006-05-03 00:00:00.000' AND
'2006-05-03 00:00:00.000'
The time that is part of tradedate is putting it outside of the
"range" of the BETWEEN; in this case of course the BETWEEN has no
range, being two identical points in time. To match it needs a 24
hour range, but that is not what it has.
To use BETWEEN you would need toto reduce tradedate to a simple date
with no time for the test.
Roy Harvey
Beacon Falls, CT
On 3 May 2006 13:40:16 -0700, "Jim Armstrong"
<armstrongjc@hotmail.com> wrote:
>OK - my post mentioned I originally used
>
>WHERE tradedate BETWEEN begindate AND enddate
>
>Begindate and enddate are not columns - they are parameters the user
>supplies in the format mm/dd/yyyy.
>
>My whole post was explaining that I would *prefer* to use the BETWEEN
>AND statement - the converting was not my choice, but it is the only
>way this query works when the user specifices the same date as
>begindate and enddate.
>
>So once again - can someone explain why this query returns no records
>when begindate and enddate are specified as 5/3/2006 and there is
>clearly trade data with that tradetime?
Navigation:
[Reply to this message]
|