UFN_ADDRESS_GETSEASONALADDRESS

            CREATE function dbo.UFN_ADDRESS_GETSEASONALADDRESS
            (
                @CONSTITUENTID uniqueidentifier,
                @DATE datetime
            )
            returns @T table 
            (
                ADDRESSBLOCK nvarchar(150),
                CITY nvarchar(50),
                STATE nvarchar(50),
                POSTCODE nvarchar(12),
                COUNTRY nvarchar(100)
            )
            as
            begin

                declare @CURRENTMONTHDAY char(4);
                declare @MONTH char(2);
                declare @DAY char(2);

                set @MONTH = cast(month(@DATE) as char(2));
                if len(@MONTH) = 1 set @MONTH = '0' + @MONTH;

                set @DAY = cast(day(@DATE) as char(2));
                if len(@DAY) = 1 set @DAY = '0' + @DAY;

                set @CURRENTMONTHDAY = @MONTH + @DAY;

                insert into @T
                    select top 1
                        ADDRESS.ADDRESSBLOCK,
                        ADDRESS.CITY,
                        STATE.ABBREVIATION,
                        ADDRESS.POSTCODE,
                        COUNTRY.DESCRIPTION
                    from
                        dbo.ADDRESS
                    left join
                        dbo.STATE on ADDRESS.STATEID = STATE.ID
                    left join
                        dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                    where
                        CONSTITUENTID = @CONSTITUENTID
                        and ADDRESS.DONOTMAIL = 0
                        and STARTDATE <> '0000'

                        --determines if @DATE is between the start date and end date for the address

                        and ((cast(@CURRENTMONTHDAY as int) - cast(STARTDATE as int)) + 1231) % 1231 between 0 and ((cast(ENDDATE as int) - cast(STARTDATE as int)) + 1231) % 1231;

                -- if no address fits the date criteria, use the primary address

                if @@ROWCOUNT = 0
                begin
                    insert into @T
                        select top 1
                            ADDRESS.ADDRESSBLOCK,
                            ADDRESS.CITY,
                            STATE.ABBREVIATION,
                            ADDRESS.POSTCODE,
                            COUNTRY.DESCRIPTION
                        from
                            dbo.ADDRESS
                        left join
                            dbo.STATE on ADDRESS.STATEID = STATE.ID
                        left join
                            dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                        where
                            CONSTITUENTID = @CONSTITUENTID 
                            and ADDRESS.DONOTMAIL = 0
                            and ADDRESS.ISPRIMARY = 1;
                end

                return;
            end