TSQL - User defined function to add months to a date represented by an integer with the format YYYYMM

I call a date represented by an integer with the format YYYYMM, a Period ID. Where the period represents a given month. This reduces the noise introduced by different ending dates on different systems, when trying to join data based on month.

/*

       -- Execution

       select dbo.getPeriodAdd( 201101, 2 );

       select dbo.getPeriodAdd( 201101, -3 );

       -- Issues

       select cast( 999912 as int ); --> OK!

       select cast( 1000003 as int ); --> OK!

       select dbo.getPeriodAdd( 209912, 3 ); --> OK!

       select dbo.getPeriodAdd( 999912, 0 ); --> OK!

       select dbo.getPeriodAdd( 999912, 1 ); --> NOT GOOD!

       select dbo.getPeriodAdd( 999912, 3 ); --> NOT GOOD!

      

       -- Manual exercises

       declare @d as date;

              set @d = getdate();

       select

              @d

              , (YEAR(@d)*100)+MONTH(@d) as PeriodID

              , DATEADD( m, -1, @d ) as LastMonth

              , ( year( DATEADD( m, -1, @d ) ) * 100 )

                     + MONTH( DATEADD( m, -1, @d ) )

                     as PreviousPeriodID

       ;

      

       -- Other period/date calculations demo

       declare @PeriodID as int;

              set @PeriodID = 201112;

       select

              cast( @PeriodID / 100 as int ) as Year

              , cast( @PeriodID % 100 as tinyint ) as Month

              , CAST(

                     cast( cast( @PeriodID % 100 as tinyint ) as varchar(2) )

                     + '/1/'

                     +  CAST( cast( @PeriodID / 100 as int ) as varchar(4) )

                     as date

              ) as Date

       ;

             

      

*/

ALTER function [dbo].[getPeriodAdd]

(

       @PeriodID as int

       , @Increment as int

)

returns int

as

begin

       -- Verify parameters

       set @Increment =

              case

                     when

                           @PeriodID = 999912

                           and @Increment > 0

                     then

                           0

                     else

                           @Increment

              end

       ;

       -- Get Date

       declare @PeriodDate date; 

       set @PeriodDate =

              dbo.getPeriodDate( @PeriodID )

       ;

      

       -- Return

       return

       (

              cast(

                     ( year( DATEADD( m, @Increment, @PeriodDate ) ) * 100 )

                     + MONTH( DATEADD( m, @Increment, @PeriodDate ) )

                     as int

              )

       );

end