YYYYMM Math

I had a project recently where I had to do math on a number that represented a date, YYYYMM.

For example, February 2013 would be stored as an integer, 201302.

As an aside, YYYYMM makes WAY more sense than ever storing anything MMYYYY.  Why?  For one, it sorts correctly.  And the biggest reason - no need to worry about leading zeroes, and therefore the data can be stored as NUMERIC, not VARCHAR data.

I needed to do month math on this data. For example, give me the previous month, six months ago, one year ago, nine months in the future, etc.  This gets tricky because though it is tempting to do -1, -6, -12, +9, the rollover of years does not work at all.

The following function solves the problem.

ALTER FUNCTION dbo.[YearMonth_Math]
(
@Yearmonth int, @months smallint
)
RETURNS INT
AS
BEGIN
RETURN
 CASE WHEN @months THEN CASE --This case handles flipping January to the previous years December.
 WHEN convert(int, Right(@Yearmonth,2)) <= abs(@months) --will we need to flip a year?
 THEN convert(int,
  convert(char(4), Left(@Yearmonth,4) + (((@Months)) / 12)  - case when right(@YearMonth, 2) + (@months % 12) < 1 Then 1 else 0 end)
  + convert(char(2), right('0' +  convert(varchar(3), (right(@yearmonth, 2) + (@months % 12) 
  + case when right(@YearMonth, 2) + (@months % 12) < 1 Then 12 else 0 end)),2))
  )
  --Otherwise, this previous month calculation is very simple.
  ELSE @Yearmonth + @months
 END
 WHEN @months >0
 THEN CASE --This case handles flipping December to the next years January.
  WHEN 12 - convert(int, Right(@Yearmonth,2)) <= @months --will we need to flip a year?
  THEN convert(int,
   convert(char(4), left(@YearMonth,4) + ((@months + right(@yearMonth,2) -1) / 12) ) +   
   convert(char(2), right('0' + convert(varchar(3), (right(@YearMonth, 2) + (@months % 12)  
   - case when right(@YearMonth, 2) + (@months % 12) > 12 THen 12 else 0 end)),2))
   )
   --Otherwise, this previous month calculation is very simple.
   ELSE @Yearmonth + @months
  END
 ELSE @YearMonth
 END
END

Here's some testing to verify all the different possibilities.

select  dbo.[YearMonth_Math] (201212,1), 201301
select  dbo.[YearMonth_Math] (201212,2), 201302
select  dbo.[YearMonth_Math] (201212,7), 201307
select  dbo.[YearMonth_Math] (201212,12), 201312
select  dbo.[YearMonth_Math] (201212,13), 201401
select  dbo.[YearMonth_Math] (201212,24), 201412
select  dbo.[YearMonth_Math] (201212,25), 201501
select  dbo.[YearMonth_Math] (201212,36), 201512
select  dbo.[YearMonth_Math] (201201,-1), 201112
select  dbo.[YearMonth_Math] (201201,-2), 201111
select  dbo.[YearMonth_Math] (201201,-7), 201106
select  dbo.[YearMonth_Math] (201201,-12), 201101
select  dbo.[YearMonth_Math] (201201,-13), 201012
select  dbo.[YearMonth_Math] (201201,-24), 201001
select  dbo.[YearMonth_Math] (201201,-25), 200912
select  dbo.[YearMonth_Math] (201212,-1), 201211
select  dbo.[YearMonth_Math] (201212,-2), 201210
select  dbo.[YearMonth_Math] (201212,-7), 201205
select  dbo.[YearMonth_Math] (201212,-12), 201112
select  dbo.[YearMonth_Math] (201212,-13), 201111
select  dbo.[YearMonth_Math] (201212,-24), 201012
select  dbo.[YearMonth_Math] (201212,-25), 201011
select  dbo.[YearMonth_Math] (201212,-36), 200912
select  dbo.[YearMonth_Math] (201206,1), 201207
select  dbo.[YearMonth_Math] (201206,-1), 201205
select  dbo.[YearMonth_Math] (201206,2), 201208
select  dbo.[YearMonth_Math] (201206,-2), 201204
select  dbo.[YearMonth_Math] (201201,1), 201202
select  dbo.[YearMonth_Math] (201201,13), 201302
select  dbo.[YearMonth_Math] (201201,25), 201402