SQL & DateTime

Introduction

I started out as a clipper developer in the 90’s.  Clipper??  What the heck is Clipper you say?  You know those “old style” languages where you had to “code” your own screens 🙂 No such things as WinForms in clipper. The database I was using at the time was Pervasive Btrieve, which was a flat file based database. So DB access at the time, if you didn’t seek first against the key (yeah, indexes did exist) was:

1 – Open file
2 – Read while Not EOF
3 – Test each record for a filter match (NO select * from MyTable where Field = Filter)
4 – If the Record matched your criteria, you would do something with it.

The awesome thing was that it was extremely fast, and mind you, the boys at Pervasive were kind enough to provide a SQL API a few years later that you could use from the next gen WinForms style apps to fire SELECT statements at Pervasive.

Be that as it may, while looping through the data, testing if a Date field matched your criteria, the results were very consistent with what you would expect! i.e. If you were looking for all records in the date range 01 Jan 2012 to 31 Dec 2012 your code would test

      
  Param1 = 01 Jan 2012
  Param2 = 31 Dec 2012

  if Date >= Param1 and Date <= Param2 
    DoSomething()

And you would actually get ALL the records.

So….what’s all the fuss?

SQL & The “Dreaded” DateTime

There are probably more elegant ways of solving this tiny issue than how I chose to get around it, but I am hopeful I will help some devs out there who rely heavily on unit tests. Lets be honest, if you dealing with millions of rows of data, how would you really know if you missed a couple? You would probably write a test script like:

 
  Param1 = 01 Jan 2012
  Param2 = 31 Dec 2012

  SELECT COUNT(*) 
  FROM MyTable 
  WHERE (Date >= Param1) AND (Date <= Param2)

To compare your output to. The problem here is, they would yield the same result, but both would be wrong.

If you take a SQL date like 2012-12-31 16:07:28.320…this record would not be included in your result set. Why? Because it is actually greater than 31 Dec 2012!

How I got around it was to add 1 day to my End Date and modify the filter as below:

 
  Param1 = 01 Jan 2012
  Param2 = 31 Dec 2012
  NewParam2 = Param1 + 1 Day = 01 Jan 2013

  SELECT COUNT(*) 
  FROM MyTable 
  WHERE (Date >= Param1) AND (Date < NewParam2)

Conclusion

When working with the DateTime field, take extra special care to ensure you get ALL the data you are looking for, and always check the data around the start and end dates to ensure you have everything. I picked this up many years ago, only because I was expecting payments to have happened on the last day of the year and didn’t have any records in my resultset to match that date.