Digicorp

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!

This entry was posted on Saturday, July 4th, 2009 at 10:02 pm and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.