Payment due date function

,

Payment due date function

This function will accept any date (@calcDate) and any given nth day (@NthDay) of the month, as an integer, for which you want to calculate a due date. First, this function will calculate the first day of the month for the passed calculation date. Second this function will determine the payment due date based on the passed nth day variable. Third, should the payment due date fall on a weekend it will automatically default to the following Monday and return the corresponding payment due date.

Example 1: If you want to calculate the 10th day of the month for October 2007 you can could write something like this:

SELECT dbo.fnPaymentDueDate(10,'10/30/2007') AS pmtDueDate

Which will return 2007-10-10 00:00:00

Example 2: The 10th day of the month for November falls on a Saturday thus a the following example would return the due date for the following Monday like so:

SELECT dbo.fnPaymentDueDate(10,'11/25/2007') AS pmtDueDate

Which will return 2007-11-12 00:00:00

May this function increase your productivity!

Sincerely,

Ralph Schwehr

DBA

CREATE FUNCTION [dbo].[fnPaymentDueDate] (@NthDay int, @calcDate datetime)
/********************************************************************************************************** 
LAST UPDATED:     10/3/2007
VERSION:     1.0
AUTHOR:     Ralph Schwehr
NAME:     fnPaymentDueDate
USAGE:     returns the nth payment due day of the month for any given date. If the nth payment due date falls
        on a weekend the function will default to the following monday
PARAMETERS:     INPUT    (the nth day of the month for the due date, 
                        calculation Date)
                 OUTPUT (the due date based on the Nth day of the month from the calculation date 
                        - if the nthDay is on a weekend this function will 
                        return the following Monday as the alternative date)
RETURNS: smalldatetime
NOTES: 10/3/2007 VERSION HISTORY 1.0 INITIAL RELEASE
**********************************************************************************************************/

RETURNS smalldatetime
AS 
BEGIN 
DECLARE @firstDayOfMonth smalldatetime
DECLARE @payDueDate smalldatetime
    -- calculate the first day of month for the passed in calcDate
    SET @firstDayOfMonth = DATEADD(mm,DATEDIFF(mm,0,@calcDate),0)
    -- subtract 1 day to account for the first day of the month
    SET @NthDay = @NthDay - 1

    SELECT @payDueDate =
        CASE DATENAME(dw,DATEADD(dd,@NthDay,@firstDayOfMonth))
            -- if the payment due date falls on a Saturday or Sunday the 
            -- following Monday will be used as the due date
            WHEN 'Saturday' THEN DATEADD(dd,@NthDay+2,@firstDayOfMonth)
            WHEN 'Sunday' THEN DATEADD(dd,@NthDay+1,@firstDayOfMonth)
            -- add n-1 days to the first day of the month to derive the nth day 
            ELSE DATEADD(dd,@NthDay,@firstDayOfMonth)
        END
    
Return @payDueDate
END

Rate

3 (2)

Share

Share

Rate

3 (2)