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

LINQ doesn't like TINYINT PK IDENTITY fields

So, to save space (and it is a bit archaic, but why not) I sometimes use tinyint and smallint for the PK IDENTITY columns in small lookup tables.

For example, a list of counties or congressional districts for a state government project.  Will never be more than 255 or negative.  Tinyint.  1 byte instead of 4.

Yeah yeah, I'm aware that in the long run, that adds up to very little saved disk space.  Very little.  Still, I can't help but be tight with data type constraints.  Its not like I will be allowing every column to be declared varchar(8000), much less varchar(max), even when "disk space is cheap".  Its not like I'll be using bigint instead of int when I know I'll never get more than 2 billion records. 

Except that when your developers want to use LINQ, you can't use a TINYINT IDENTITY column. Stupid error.

Changed it, begrudgingly, to smallint.  2 bytes instead of 4.  Grrr...

Read more:
http://linqinaction.net/blogs/roller/archive/2007/11/28/linq-to-sql-doesn-t-support-identity-tinyint.aspx