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?
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.