Checking date functions

Seems whenever I write a function that affects dates, I want to make sure it works. I wrote an article here recently about @@datefirst, and naturally, I wanted to make sure I wasn't writing something that was just plain wrong.

This is where you find yet another use for an auxiliary table of numbers. I'm pleased to say that I was a big fan of having a dbo.nums table long before I had ever heard of Itzik Ben-Gan, although he has typically taken the 'number of uses' list to the extreme.

So how does it come in handy for testing date functions? Easy! Because I can use it to create a list of dates. Here, I get a list of 500 dates, starting from December 20th, 2005.

select dateadd(day,num,'20051201') as adate
from dbo.nums
where num < 500

Here, I get a list of 100 times in 2 hour intervals, starting from right now!

select dateadd(hour, 2 * num, getdate()) as adate
from dbo.nums
where num < 100

So now I can very easily test my date functions.

select dbo.myfunction(adate)
from
  (select dateadd(hour, 2 * num, getdate()) as adate
  from dbo.nums
  where num < 100) d

It's worth noting here, that it's always best to generate a list of numbers that starts before the area you're interested in, and goes past it too. That way, you can check what happens at the edges of years, and things like that.

Another thing that you should check is that it works correctly for different values of @@datefirst. A simple while loop can help you do that.

declare @df int
set @df = 1
while (@df <= 7)
begin
    set datefirst @df
    /* Check your function here */

    set @df = @df + 1
end

With these simple things, you should be able to write good tests for your functions. The visual results can make life really easy to see where you're going wrong too.
Published 07 September 2006 04:07 by Rob Farley

Comments

# A couple of short datetime articles

07 September 2006 04:27 by Rob Farley
One about @@datefirst at http://sqlblogcasts.com/blogs/rob_farley/articles/999.aspx and one about testing...