between operator, inclusive or not?

[Anti-Rich]

hi all

im encountering weird result data when using a between in my sql statement... this is my sql statement:

[SIZE=2][COLOR=#800000]SELECT DISTINCT EmployeeID, EntryDate FROM tblCapture WHERE EmployeeID = @currentEmployee [/COLOR][/SIZE]
[SIZE=2][COLOR=#800000]AND EntryDate BETWEEN @date1 AND @date2 ORDER BY EntryDate ASC[/COLOR][/SIZE]

my question is, is the between clause in an sql statement inclusive of the parameters supplied?
i checked it on msdn and it seems so, but for some reason, my 'date2' date is not showing up in my results, when i know it should.
but my 'date1' date shows up no problems...

can anyone help me? any feedback or ideas or suggestions would be greatly appreciated.

cheers
adam

 

[JohnH]

It is inclusive, it could be due to time part, see this and the last paragraph about datetime values: http://msdn2.microsoft.com/en-us/library/ms187922.aspx

 

[cjard]

Whether inclusive or not, remember that date values refer to a millisecond in time..

If you say between 01-JAN-1901 and 03-JAN-1901

you wont see any results for 03 jan unless they occur at exactly midnight, and 00 miliseconds. Even 1 milliseconds past midnight is outside the range


For the same reason that 4.001 is outside the range 1.000 to 4.000


When working with dates, we never use BETWEEN because you cant immediately see whether it is inclusive or not and we ALWAYS use the notion that the IT world adopts for ranges:

lower <= x < upper


So your query becomes:

WHERE @date1 <= record_date AND record_date < @date2


@date1 is inclusive
@date2 is exclusive


This saves so much messing around...

 

[Arg81]

I never knew that way...I've always been using BETWEEN in my apps....

..off to make a few changes!!

 

[Anti-Rich]

hi cjard

ok, this is the modified query

[SIZE=2][COLOR=#800000]SELECT DISTINCT EmployeeID, EntryDate FROM tblCapture 
WHERE (EmployeeID = @currentEmployee)
AND (@d1 <= EntryDate AND EntryDate < @d2) ORDER BY EntryDate ASC
[/COLOR][/SIZE]

ok, it still isnt including date2. i tried modifying that '<' to '<=' but it still didnt work!! both dates need to be inclusive as i am checking the two supplied dates as well.

am i missing something?
sorry about this.

cheers
adam

 

[TechGnome]

Whether inclusive or not, remember that date values refer to a millisecond in time..

If you say between 01-JAN-1901 and 03-JAN-1901

you wont see any results for 03 jan unless they occur at exactly midnight, and 00 miliseconds. Even 1 milliseconds past midnight is outside the range


For the same reason that 4.001 is outside the range 1.000 to 4.000


When working with dates, we never use BETWEEN because you cant immediately see whether it is inclusive or not and we ALWAYS use the notion that the IT world adopts for ranges:

lower <= x < upper


So your query becomes:

WHERE @date1 <= record_date AND record_date < @date2


@date1 is inclusive
@date2 is exclusive


This saves so much messing around...

Which is why we make it a standard practice that dates are saves sans the time.... avoids this time problem as well. Only in a few, rare cases do we store the time. It also allows BETWEEN to work for us as expected....

-tg

 

[TechGnome]

Does EntryDate contain the time? If so, and you are expecting records with that date to show, then you actually need to add a day to it, then use < (no equal) on order for them to be picked up.

-tg

 

[Anti-Rich]

Which is why we make it a standard practice that dates are saves sans the time.... avoids this time problem as well. Only in a few, rare cases do we store the time. It also allows BETWEEN to work for us as expected....

-tg

'saves sans the time' - sorry, i dont know what you mean by this...

i dont store the time, it shows up as midnight in the db, but it STILL doesnt include the latter date...

any ideas?

(i will try the +1 day method)

cheers mate
regards
adam

 

[cjard]

Please note in my original post, I said that d2 is exclusive

date1 <= record_date < date2

if you want records for 01 jan and 02 jan then you must have:

01 jan <= record_date < 03 jan


because dates store a fractional component of time too, that query above translates to:

"from midnight on the first of jan, all through the day, all through 02 jan also, and up to but not including midnight on 03 jan"


hence the range you get is:

01 jan 00:00:00.0
to
02 jan 23:59:59.999999999999999999999999999999999999999999999999999999999999999999...


Do you see how this is the only way to ensure ranges that join perfectly and do not overlap?

0 <= x < 10 'from 0 to 9.9999...
10<= x < 20 'from 10 to 19.9999...


-
Yep, use the date2+1day method, or educate your users that they must enter the day after the end of the range they want..

Human says: "I want everything from the first of january to the 31st"
Computer must be asked (for absolute certainty): "Give everything fromincluding 01 jan toexculding 01 feb"


Hopefully this is more clear. We chose to re-educate the users to be more precise in their thinking!



Oh, and "sans" is french for "without", so TG implies he removes the fractional time component from all his dates as they are entered, thus, essentially only whole integer numbers are being stored (Dates are stored as integer days + fractional hours, so a date of 1.5 is 1 day, 12 hours since the Epoch.
The Epoch is defined differently in various systems, but for sql server, I think its either 01 jan 1901, or 01 jan 1699. A date stored as 0 is 01 jan 1901 + 0. A date stored as 2.75 is 03 jan 1901 18:00:00"

It very important that you note this well! It will help you understand much about date and time mathematics in databases, and why date2 - date1 = a number of days and fractional hh mm ss ....

 

[Arg81]

if you want records for 01 jan and 02 jan then you must have:

01 jan <= record_date < 03 jan

hmmm, this is interesting. Unless I've read what you've wrote in the complete wrong context then this doesn't seem to be the case for me.

I have no need to record time in my SQL, so all of my Date fields are set to SmallDateTime.

My SQL queries to return results looks like;
SELECT * FROM Table WHERE Date Between @StartDate AND @EndDate

On my form 2 DateTimePickers set @StartDate and @EndDate.

if @EndDate is set as today, then I DO get all records that the Date matches today...

I may of got the wrong end of the stick, if so just ignore me, it's Friday and I've started drinking early (I wish!!!!)

 

[cjard]

Does EntryDate contain the time? If so, and you are expecting records with that date to show, then you actually need to add a day to it, then use < (no equal) on order for them to be picked up.

-tg

do note that if the date2 also contains a time, and you add a day, then youre effectively asking for dates up to but not including that tiem also..


date1 <= record_date < date2

user passes in @date 2 which is 03 jan 1901 18:00:00 <-- it contains a time component too!
records will be returned that have dates on 03 jan for any time before 6pm too!

if youre purely after hard dates:

TRUNCATE(date1) <= record_date < TRUNCATE(date2 + 1)

truncate ensure the time component is removed from the passed in dates.
Try to do it this way rather than calling truncate on every date in the table.. When doing db work we always try to manipulate our few parameters to match how the data is stored rather than manipulate many thousands or millions of data items to match our parameters

 

[cjard]

if @EndDate is set as today, then I DO get all records that the Date matches today...

I may of got the wrong end of the stick, if so just ignore me, it's Friday and I've started drinking early (I wish!!!!)

BETWEEN is inclusive: date1 <= record_date <= date2
Your dates have the times lopped off
It is integer comparison:

01 jan 1901 has a value of 0
02 jan has a value of 1
03 jan 1901 has a value of 2

BETWEEN 0 and 2 is
0 <= record_date <= 2

record date 2s are included
i.e. 03 jan 1901 is included


Have one for me...