SQL Server T-SQL User-Defined Function of the Week

Volume 1 Number 31     June 17, 2003

The importance of Testing!

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Testing! Got to do it. That's what this issue is about. And what goes
with testing? Bugs, of course. 

You should have received a message this morning with a revised 
version of udf_Txt_CharIndexRev. It corrects an error in that 
function.

Next week I'll publish a couple of stored procedures that allow
convenient regression testing of that function. Had I written
them earlier, the bug might not have been published. 

The stored procedures are not ready for publication but this week's 
issue is about a different bug that I found in a different piece of code.

I found a useful UDF on planet-source-code a few weeks ago. 
It used a very good idea about how to round a date down to the 
beginning of a part of a datetime. You use the name of the 
datepart from the DATEPART built-in function to tell it how to
round.

So if your date was '2003-07-01 14:03:43.231' then this table 
shows how the function should work 

Datepart        Result
--------------  --------------------------------------
 year           2003-01-01 00:00:00.000
 quarter        2003-07-01 00:00:00.000
 month          2003-07-01 00:00:00.000
 week           2003-06-29 00:00:00.000
 day            2003-07-01 00:00:00.000
 hour           2003-07-01 14:00:00.000
 minute         2003-07-01 14:03.00.000
 second         2003-07-01 14:03.43.000

The problem with the original function was that it was incorrect.
It produced the wrong result for week and second dateparts. Not
that these are the most important dateparts to round, but a
function shouldn't be shipped with bugs.

To manage the testing of UDFs I've been putting at least a few
tests into the header comment of each scalar UDF.  These are 
rarely a comprehensive test but at least they give a quick 
indication if the UDF works.  This is particularly useful after
making a minor change.  The header tests can show you if the 
UDF is messed up by a change.

Lets look at the UDF and I'll have more to say about testing
once we put the UDF to use. Here's the CREATE FUNCTION script:

/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_DT_Round2Part (

    @InDate   datetime     -- The datetime to round
  , @DatePart nvarchar(20) -- Datepart to round to Uses the same
                           -- values as built-in DATEPART
)   RETURNS datetime       -- @InDate rounded to the @DatePart
    WITH SCHEMABINDING
/* 
* Returns a date rounded to a named date part.  For most 
* date parts this is done by getting the quantity of the unit 
* since the zero date.  Then this number is added to a date of
* zero.  This gives a datetime that is rounded to the datepart
* requested.
* Returns NULL for a bad DATEPART. Language sensitive.
*
* Attribution: based on similar UDF by James Travis on Planet
* Source Code. This version corrects errors in wk and ss
*
* Example:
select dbo.udf_DT_Round2Part (getdate(), 'mm') -- month start
*
* Test:
PRINT 'Test 1    ' + CASE WHEN '2003-04-01'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'mm')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 2    ' + CASE WHEN '2003-04-04 10:00:00'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'hour')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 3    ' + CASE WHEN '2003-04-04 10:19:00'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'mi')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 4    ' + CASE WHEN '2003-04-04 10:19:32'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'ss')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 5    ' + CASE WHEN '2003-03-30 00:00:00'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'wk')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 6    ' + CASE WHEN '2003-03-30 00:00:00'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'wk')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 7    ' + CASE WHEN '2003-03-30 00:00:00'=
         dbo.udf_DT_Round2Part('2003-03-30 00:00:00.00', 'wk')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 8    ' + CASE WHEN 
dbo.udf_DT_Round2Part('2003-04-04 10:19:00', 'foobar') IS NULL
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 9    ' + CASE WHEN 
dbo.udf_DT_Round2Part('2003-04-04 10:19:00', NULL) IS NULL
                     THEN 'Worked' ELSE 'ERROR' END
*
* History:
* When        Who     Description
* ----------- --- ----------------------------------------------
* 2003-05-04  ASN Initial Coding, see attribution
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Look for an article about this UDF in the UDF of the Week
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

SET @DatePart = LOWER(@DatePart) -- In case of CS collation

RETURN CASE WHEN @InDate IS NULL -- Be explicit about this case.
                THEN NULL
            WHEN @DatePart IN ('year', 'yy', 'yyyy') 
	            THEN DATEADD(yyyy,DATEDIFF(yyyy,0,@InDate),0)
            WHEN @DatePart IN ('quarter', 'qq', 'q')
	            THEN DATEADD(qq,DATEDIFF(qq,0,@InDate),0)
            WHEN @DatePart IN ('month', 'mm', 'm')
                THEN DATEADD(mm,DATEDIFF(mm,0,@InDate),0)
            WHEN @DatePart IN ('day', 'dd', 'd', 'dy', 'y',
                                'dayofyear', 'dw', 'weekday')
                THEN DATEADD(dd,DATEDIFF(dd,0,@InDate),0)
            WHEN @DatePart IN ('wk','w','week')
                THEN DATEADD(DD 
                           , -1 -- SUBTRACT ONE DAY
                           , DATEADD(WK
                                   , DATEDIFF(WK,0,@InDate)
                                   , 0
                                    )
                            )
            WHEN @DatePart IN ('hh','hour')
                THEN DATEADD(hh,DATEDIFF(hh,0,@InDate),0)
            WHEN @DatePart IN ('mi','n','minute')
                THEN DATEADD(mi,DATEDIFF(mi,0,@InDate),0)
            WHEN @DatePart IN ('ss','s','second')
                THEN DATEADD(ms -- Subtract the ms from the time.
                            ,-1*DATEPART(ms,@InDATE) 
                            ,@InDATE)
            ELSE
                NULL -- When unknown @DatePart
            END
END
GO

GRANT EXEC, REFERENCES ON dbo.udf_DT_Round2Part to [PUBLIC]
go
\-------Stop copying above this line ---------------------------/

As you can see, inside the header is a Test section. The test 
section doesn't have asterisks at the start of each line like
the rest of the header.  That's because it's set up to run in 
Query Analyzer by selecting the lines of the test and using 
the F5 key or the execute button.

Instead of printing the result, each tests compares the result
of the function to a know value.  I do that because it's much 
easier to evaluate the result of the test this way.  All I you
have to do is look at the output of the tests.  If each test
returned a datetime, you'd have to figure out what the correct
result should have been.  That's not so hard while you're 
developing the function.  It becomes more difficult months later
when you're making a minor change but want to know if the UDF 
still works.

Lets run the tests:
/------- Start copying below this line -------------------------\
PRINT 'Test 1    ' + CASE WHEN '2003-04-01'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'mm')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 2    ' + CASE WHEN '2003-04-04 10:00:00'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'hour')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 3    ' + CASE WHEN '2003-04-04 10:19:00'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'mi')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 4    ' + CASE WHEN '2003-04-04 10:19:32'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'ss')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 5    ' + CASE WHEN '2003-03-30 00:00:00'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'wk')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 6    ' + CASE WHEN '2003-03-30 00:00:00'=
         dbo.udf_DT_Round2Part('2003-04-04 10:19:32.233', 'wk')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 7    ' + CASE WHEN '2003-03-30 00:00:00'=
         dbo.udf_DT_Round2Part('2003-03-30 00:00:00.00', 'wk')
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 8    ' + CASE WHEN 
dbo.udf_DT_Round2Part('2003-04-04 10:19:00', 'foobar') IS NULL
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 9    ' + CASE WHEN 
dbo.udf_DT_Round2Part('2003-04-04 10:19:00', NULL) IS NULL
                     THEN 'Worked' ELSE 'ERROR' END
\-------Stop copying above this line ---------------------------/
(Results)
Test 1    Worked
Test 2    Worked
Test 3    Worked
Test 4    Worked
Test 5    Worked
Test 6    Worked
Test 7    Worked
Test 8    Worked
Test 9    Worked

So it's pretty easy to tell if the tests worked.  Of course,
it is just as easy to make a mistake in coding the test than
in coding the function but without the testing it's just to 
easy to leave a mistake in the code.

The next script shows the results shown in the table at the 
start of this article.
/------- Start copying below this line -------------------------\
DECLARE @TestDT datetime
SET @TestDT = '2003-07-01 14:03:43.231'
SELECT dbo.udf_DT_Round2Part (@TestDT, 'year') [To Year]
     , dbo.udf_DT_Round2Part (@TestDT, 'qq')   [To Qtr]
SELECT dbo.udf_DT_Round2Part (@TestDT, 'mm')   [To Mon]
     , dbo.udf_DT_Round2Part (@TestDT, 'wk')   [To Week]
SELECT dbo.udf_DT_Round2Part (@TestDT, 'dy')   [To Day]
     , dbo.udf_DT_Round2Part (@TestDT, 'hh')   [To Hr]
SELECT dbo.udf_DT_Round2Part (@TestDT, 'mi')   [To Min]
     , dbo.udf_DT_Round2Part (@TestDT, 'ss')   [To Sec]
GO
\-------Stop copying above this line ---------------------------/
(Results)
To Year                    To Qtr                     
-------------------------- -------------------------- 
2003-01-01 00:00:00.000    2003-07-01 00:00:00.000

To Mon                     To Week                    
-------------------------- -------------------------- 
2003-07-01 00:00:00.000    2003-06-29 00:00:00.000

To Day                     To Hr                      
-------------------------- -------------------------- 
2003-07-01 00:00:00.000    2003-07-01 14:00:00.000

To Min                     To Sec                     
-------------------------- -------------------------- 
2003-07-01 14:03:00.000    2003-07-01 14:03:43.000

Now that it's been tested this is a useful function that I'm 
sure I'll find many reasons to use.

One more thing.  I don't know where, but I'm sure that to one
degree or another that I've published code with bugs also.
Testing shouldn't be about blame. It should be about finding
and eliminating bugs in code and moving on from there.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+