Convert a date type into year&month format

I am sure some other people have blogged this somewhere, but I'd like to add this to my list of topics because I have encountered this type of requirement quite often.

Basically, let's say we have a column called 'shipped date' that has data like 'YYYY-MM-DD'. If we want to change this data so it displays only year and month, like 'YYYY-MM' or 'YYYYMM', then how would we do it?

First of 'YYYY-MM-DD' is date datatype but once we change it to 'YYYYMM', it no longer is date anymore. Therefore a database function 'To_char' is needed for such a conversion.

In OBIEE, there is a function 'evaluate' that can help executing database functions within the Admin tool.

Therefore, the entire statement is something along this line:
EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), "Purchase_Facts"."PO_DATE", 'YYYYMM')
if that's all we want to do.

Here, %1 and %2 are the number of parameters we are passing. We are dealing with only 2 parameters in this case: year and month, so we have %1 and %2.

So having followed the syntax, we define this statement in physical column mapping, which is in the LTS of the logical table in BMM layer, as you can see in the below testing sample:



This gives the physical definition of the column to be 'YYYYMM' instead of the original 'date' data.

Try it and test it for yourself..

Thanks
Until next time.