UFN_CONSTITUENTRECOGNITION_CALCULATEEXPIRATIONDATE

      create function dbo.UFN_CONSTITUENTRECOGNITION_CALCULATEEXPIRATIONDATE 
            (
                @RECOGNITIONPROGRAMID uniqueidentifier,
                @STARTDATE datetime
            )
            returns datetime
            as
            begin
                declare @CUTOFFDATEFORYEAR char(4)
                declare @EXPIRATIONDATE datetime
                declare @MONTH nvarchar(2)
                declare @DAY nvarchar(2)
                declare @BACKDATEMEMBERSHIPS bit

                select 
                    @CUTOFFDATEFORYEAR = CUTOFFDATEFORYEAR,
                    @BACKDATEMEMBERSHIPS = BACKDATEMEMBERSHIPS
                from dbo.RECOGNITIONPROGRAM 
                where ID = @RECOGNITIONPROGRAMID;

                set @EXPIRATIONDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@STARTDATE,1)

         -- If set expiration enabled

         if @BACKDATEMEMBERSHIPS =1 
         begin               
                    set @MONTH = SUBSTRING(@CUTOFFDATEFORYEAR,1,2)
                    set @DAY  = SUBSTRING(@CUTOFFDATEFORYEAR,3,2)

            if @MONTH < MONTH(@STARTDATE)
                set @EXPIRATIONDATE = dateadd(year, 1, @EXPIRATIONDATE)
          else if @MONTH = MONTH(@STARTDATE) and @DAY <= DAY(@STARTDATE)
              set @EXPIRATIONDATE = dateadd(year, 1, @EXPIRATIONDATE)
         end

                return @EXPIRATIONDATE

            end