NW Business Intelligence

thoughts on technology, B.I., and more…

Archive for June, 2008

Getting year and month

Posted by Brad Greene on June 27, 2008

It’s fairly common for data warehouse date dimensions to have a column that combines the year and month into a 6 digit integer. It’s useful for sorting and other situations where you need a value that can be manipulated mathematically. Other times this format comes from external data. An example would be 200803 for March, 2008.

Sometimes I need to get the year part of it and I don’t have access to the other date columns that would yield just the year so I just use the Round function. This example is from Cognos Report Studio, other tools will have a similar function.

_round([MonthID]/100,0)

For example, 200806, would result in 200806/100 = 2008.06, rounded to zero places or 2008. Casting to strings and extracting substrings will work but unless you need the end result as characters it’s less efficient to do it that way.

Posted in Tech Tips | Comments Off on Getting year and month