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.

Here's some testing to verify all the different possibilities.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

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

## No comments:

Post a Comment