If you want to extract the year, month, day,… part of a date or timestamp then lots of functions are available to fulfill your job.

In DB2 we have:

In Oracle we have:

But maybe the trunc-function is a far more better alternative?

In some database sql-languages (like DB2 & Oracle) a trunc (=Oracle) and trunc_timestamp (=DB2) function exists.

The purpose of this function is:  the TRUNC_TIMESTAMP (date,fmt) function returns a timestamp that is the expression,
truncated to the unit that is specified by the format-string. Instead of the above functions trunc/trunc_timestamp returns a truncated date/timestamp-type and not a char- or number-type.

Example: if   current_date is 1897-12-04-12:22:22 (YYYY-MM-DD HH24:MI:SS) then the results of the functions below are :

So what are the benefits in Web Intelligence with objects created based on the trunc-function? 

Suppose you created a Quarter-object and you already (like every good universe-designer) formatted the object with the format ‘YYYY/MM’ in the universe, and you want to aggregate  a measure on year-quarter-level, then one dimension object is enough to fulfill the job. Without the trunc-objects you will need at least two objects (Year, Quarter) to do the job.

But that’s not all…

In some cases you need the first day of a specific period, in that case the trunc-function always brings you to the first day of the unit specified in the fmt-string. Example: if you need the first day to the current month use the function trunc(current_date,’MM’) or the first day of the current quarter use trunc(current_date,’Q’), or the starting day of the week trunc(current_date,’DAY’),…

About these ads