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