UFN_JOBSCHEDULE_DATEVALID

Returns whether a date fits a start and end date time pair for a job.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@dt	datetime	IN
@OCCURRENCETYPE	tinyint	IN
@STARTDATETIME	datetime	IN
@STARTMONTHDAY	UDT_MONTHDAY	IN
@ENDDATETIME	datetime	IN
@ENDMONTHDAY	UDT_MONTHDAY	IN

Definition

Copy



            create function dbo.UFN_JOBSCHEDULE_DATEVALID(@dt datetime, @OCCURRENCETYPE tinyint, 
                        @STARTDATETIME datetime, @STARTMONTHDAY dbo.UDT_MONTHDAY, 
                        @ENDDATETIME datetime, @ENDMONTHDAY dbo.UDT_MONTHDAY) returns bit with execute as caller
            as
            begin
                declare @retval as bit;
                declare @MONTH as int;
                declare @DAY as int;

                if @dt is null  
                    return 0;

                if @OCCURRENCETYPE = 2 --ongoing

                    return 1;

                if @OCCURRENCETYPE = 0 --onetime

                    if @dt between @STARTDATETIME and @ENDDATETIME
                        return 1;

                if @OCCURRENCETYPE = 1 --recurring                    

                    set @MONTH = month(@dt);
                    set @DAY = day(@dt);

                    if @STARTMONTHDAY <= @ENDMONTHDAY
                        begin
                            if (@MONTH > left(@STARTMONTHDAY,2) 
                                or (@MONTH = left(@STARTMONTHDAY,2) and @DAY>= right(@STARTMONTHDAY,2)))
                                and
                                (@MONTH < left(@ENDMONTHDAY,2) 
                                    or (@MONTH = left(@ENDMONTHDAY,2) and @DAY<= right(@ENDMONTHDAY,2)))  

                                return 1;
                        end
                    else
                        begin
                            if (@MONTH > left(@STARTMONTHDAY,2)
                                or (@MONTH = left(@STARTMONTHDAY,2) and @DAY>= right(@STARTMONTHDAY,2)))
                                or (@MONTH < left(@ENDMONTHDAY,2)
                                    or (@MONTH = left(@ENDMONTHDAY,2) and @DAY<= right(@ENDMONTHDAY,2)))  
                                return 1;
                        end

                return 0;
            end