Most of the time we grab the date and time using sql server GetDate() Function.
Sometimes if data is filtered between two dates it may give erroneous result. Why? Because while retrieving data using date as filter criteria, it will fetch records which are nearest 3 milliseconds.
It will be more clear by following example.
Create Table datetesting
(
PK_id INT IDENTITY,
Datefield DateTime
)
Insert into datetesting(DateField)
Values (’1/2/09 00:00′)
Insert into datetesting(DateField)
Values (’1/2/09 23:59′)
Insert into datetesting(DateField)
Values (’1/2/09 11:59:59.995 pm’)
Insert into datetesting(DateField)
Values (’1/3/09′)
Now Execute the following query
Select * from datetesting where Datefield Between ’1/2/9′ AND ’1/2/9 11:59:59.998 PM’
Result of the above query
PK_id DateField
—————————————
1 2009-01-02 00:00:00.000
2 2009-01-02 23:59:00.000
3 2009-01-02 23:59:59.997
Now, Try the following query to retrieve data. I have just added 1 millisecond and it will retrieve date 2009-01-03 00:00:00.000 which does not match the criteria.
Select * from datetesting where Datefield Between ’1/2/9′ AND ’1/2/9 11:59:59.999 PM’
PK_id DateField
—————————————
1 2009-01-02 00:00:00.000
2 2009-01-02 23:59:00.000
3 2009-01-02 23:59:59.997
4 2009-01-03 00:00:00.000
So next time when you face wrong data by filter, don’t get perplexed, This may be the reason!
Good job Kalpesh. Nice article. I was never new this fact.
Good wokr Nice fact about Datetime Filters
I personally executed the all of the above queries. Great information.
keep it up !