I've had a hard time making unit test for most of my queries because they are tied very closely to current dates and data which is added to the database could modify the result of the unit test.
For the most part I've been able to avoid problems by relying on a second version of the database filled with immutable dummy data. So my unit tests can go on with expected results without much of a hitch.
But there are some queries that I still don't know what to do with. I have a query that fetches two columns, a date and an associated value from a table based on:
WHERE Date > Now()
So my enemy here is that tomorrow isn't today. I am using the actual table not a dev version of this table for the test as data entered in this table is sensibly static and shouldn't be changed once added. It's like a reference table so to speak.
If I were to make an immutable dev version of the table then my problem would be that eventually the table's dates will in two years time pass the latest date within the table and thus result in zero records found.
And regardless of whether I had a dev version or a real version, the results for this table will always change based on the current date.
Why does an answer come only once I have finished writing my question out. A thought just occurred to me, which I must say, I want to avoid, but it seems to me that in order to make a unit test function, I would have to have my unit test generate the table before running the query. But then, do I write a unit test for the version of the unit test that populates that table??
Is there a more sensical way of achieving this goal?
Conclusion
All the answers are valid. The one I selected as the accepted answer is the one I believe to be closer to a standard tdd way (a purist viewpoint), however it's more complex and annoying. The other answers though are very simple and nice, should not be overlooked. In fact the little database trick that RolandoMySQLDBA mention is genious for mysql tests.
Since I'm working with Access, For now what I am doing is making a default db which will be programmatically appended to the dev tables which are static. When time permits that table will have it's own append table that will alter the dates to work with the now() query.
For now() I guess that will do, time permitted.
Best Answer
Why I would not change the application
I do not like the answers which propose to change the application to fulfill testing needs. To me this is the most wrong way to go. The implementation should only be driven by concepts, requirements and design decisions of the application, not of any helper tools used while implementing.
Use dynamic fixtures instead
It is a very basic concept of all tests to set up a testing environment, also called test fixtures. This concept found its way into the unit testing as well, see for example fixtures in Rails, Python or Java. All propose to use fixtures for preparing specifically crafted databases:
https://github.com/junit-team/junit/wiki/Test-fixtures
http://guides.rubyonrails.org/testing.html#the-low-down-on-fixtures
https://docs.python.org/2/library/unittest.html
Nothing stops you from populating the test database dependent on the current date!
Actually this is pretty common anyway: For example every Rails object one creates gets the current timestamp as
created_atandupdated_at. Since they are created as part of preparing the fixture, all Rails objects are created with the date of the current test and thrown away afterwards.I do not see a reason to verify the test fixture. If something is wrong concerning e.g. consistency, your application should throw an exception. To make sure that the tests cover all situations is your responsibility and no automatic verification will answer this question anyway.