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

Volume 1 Number 38   August 5, 2003

Adding an Interval to a Datetime in T-SQL

Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.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

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

The only data types that SQL Server provides for storing time
related information are datetime and smalldatetime.  Both store
both a data and a time together.  

Other relational databases and the newer SQL standards have 
additional time related data types such as date, time, and 
timespan.  Timespans are also refered to as intervals or 
durations.

The timespan comes in handy when you want to store a length of 
time.  Although there may are other methods, I've resorted to 
storing timespans in datetime columns as the difference from the 
zero hour:1900-01-01 00:00:00.  This works so long as you're 
careful about which columns are timespans and which ones 
represent points in time.

Once I've stored timespans, I always end up wanting to add them
to a datetime that represents a point in time and that's the task 
performed by this week's UDF, udf_DT_AddTime. 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_AddTime (

    @InputDT  datetime -- add from this time
  , @Timespan datetime -- timespan to add. 
)   RETURNS datetime -- Result of adding @Interval to @InputDT
    WITH SCHEMABINDING -- 
/* 
* Adds an interval to a datetime.  This works with any duration
* that is represented in a datetime.  When adding whole units
* use DATEADD instead.  This function is best when @Timespan
* consists of multiple units (03:15:00) or when it's been
* calculated and stored in a variable.
*
* Example:
select getdate(), dbo.udf_DT_AddTime(getdate(), '00:23:00')
*
* Test:
PRINT 'Test 1    ' + CASE WHEN '2003-07-10 01:10:08' = 
                dbo.udf_DT_AddTime ('2003-07-10', '01:10:08')
                     THEN 'Worked' ELSE 'ERROR' END
* History:
* When       Who Description
* ---------- --- -----------------------------------------
* 2002-06-23 ASN Initial Coding
* 
* © 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.
* Published as the T-SQL UDF of the Week for Vol 1 #38 8/5/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

DECLARE @DifferenceMSec bigint -- milliseconds in interval

-- Convert to milliseconds by taking the difference from the 
-- zero hour
SET @DifferenceMSec = DATEDIFF(ms
                             , CONVERT(datetime, '00:00:00', 8)
                             , @Timespan)

RETURN DATEADD(ms, @DifferenceMSec, @InputDT)

END
GO

GRANT EXEC on dbo.udf_DT_AddTime to PUBLIC
GO
\-------Stop copying above this line ---------------------------/

SQL Server has the built-in DATEADD function, so you want to be
sure and use it instead of udf_DT_AddTime when you're adding
a whole unit of time.  But udf_DT_AddTime comes in handy when
the timespan to add has multiple time periods in it such as 
04:13:32.  It's also useful when the timespan is in a SQL variable
or column.

Here's a simple example of using udf_DT_AddTime:
/------- Start copying below this line -------------------------\
DECLARE @Now datetime
SET @Now = getdate()

SELECT @Now as [Now]
     , dbo.udf_DT_AddTime(@Now, '00:5:30') [Now Plus 5min 30 sec]
     , dbo.udf_DT_AddTime(@Now, '1900-01-02 10:00:00') 
                                           [Now plus 1 day 10Hr]
GO
\-------Stop copying above this line ---------------------------/
(Results - 1st two columns)
Now                      Now Plus 5min 30 sec    
------------------------ ------------------------
2003-08-04 11:03:52.560  2003-08-04 11:09:22.560 

(Results - 3rd column)
Now plus 1 day 10Hr           
------------------------------
2003-08-05 21:03:52.560  

Using '00:05:30' to represent 5 minutes and 30 seconds seems 
pretty natural.  However, an unfortunate aspect of using the 
datetime data type to store timespans is that you must express 
any interval that is over one day as a specific date, usually 
in 1900.  That's why '1900-01-02 10:00:00' is used to represent 
one day and ten hours.  But until SQL Server has a timespan
data type, we'll have to live with using datetime.


+--------------------------------------------------------------+
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
+--------------------------------------------------------------+