Today on the leap-day 2016, I found out, that some of my queries didn't work like I expected. I missed data with the entry '20160229'. So I began to investigate and isolated the piece of code, that made the difference. Then I wrote wo little queries which where joined via the EXCEPT clause. I expected a result-set of zero, but I got all the data with the leap-day as the entry. (First query: rowcount=6638 , second query, rowcount=5568)
I have some trouble to find out the reason why I get deviations, what's the reason?
-- Query was executed on 2016/02/29, so GetDate() was returning '2016-02-29 15:20:26.640' for ex.
SELECT fa.FAX_ABJMT, fa.FAX_BISJMT
FROM [tLsb_FA] fa
where 1=1
and (FA.FAX_BISJMT) >= cast('20160229' as datetime) --leap-day, casted from a varchar
and FA.FAX_ABJMT <= cast('20160229' as datetime)
EXCEPT
SELECT fa.FAX_ABJMT, fa.FAX_BISJMT
FROM [tLsb_FA] fa
where 1=1
and (FA.FAX_BISJMT) >= getDate()
and FA.FAX_ABJMT <= getDate()
I do "Less than following day" rather than "Less than or equal to today". This is particularly import for leap day I suppose ("< 1st-day-of-March" may be more reliably calculated than "<= last-day-of-February" )
The issue is usually to do with values that are DATE+TIME, rather than just DATE.
Does your [FAX_ABJMT] column include time? or have a datatype of DATETIME ?
@Kristen: you were on the right track, the columns that I used are from datatype DATETIME. But I forgot, that only the DATE part is stored, time-part is set to '00:00:00', so query with GetDate() delivered a result-set of zero (correct!).
As I used "cast('20160229' as datetime)" I didn't realized, that I should have build the query with the correct instruction "cast('20160229' as DATE)" ... so that's all. Sometimes it's better to go one step back, and read it from the distance
Interesting. I wouldn't have expected them to have caused a different result (as the LIMITS of a RANGE test). They both give you midnight on 29-Feb-2016, which should be fine (I don;t think it makes any difference whether they include a Time component, or not).
Perhaps casting to DATETIME is causing the expression "precision" to be increased from DATE to DATETIME ? (Still not seeing how that makes any difference though).
Just bothered that this might not be the solution to the problem, albeit that it appears to have fixed it for now!
)