Sunday, February 17, 2013

OBIEE - TO_DATETIME the secret function

Did you know there are secret functions in OBIEE? TO_DATETIME is such a function.
This useful function appears in the book (Metadata Repository Builders Guide - here)  but not in the function lists in the UI (at least not on my 11.1.1.6.4). I'll upgrade to 11.1.1.6.8 soon and update here if it's less secret there.
To be fair, you can see this function in the Administration Tool, but there is no reason not to use it in analysis directly.

What does it do? It lets you control the date format of a given date & time string and converts it. No longer the tyranny of specific formats of the cast function. For example the following returned the timestamp I needed: 
to_datetime('01/01/2001 21:00', 'dd/mm/yyyy hh:mi')

So what the good book says?

This function converts string literals of dateTime format to a DateTime data type.
Syntax
TO_DATETIME('string1', 'DateTime_formatting_string')
Where:
string1 is the string literal you want to convert
DateTime_formatting_string is the DateTime format you want to use, such as
yyyy.mm.dd hh:mi:ss. For this argument, yyyy represents year, mm represents month,
dd represents day, hh represents hour, mi represents minutes, and ss represents
seconds.
 
Examples
SELECT TO_DATETIME('2009-03-03 01:01:00', 'yyyy-mm-dd hh:mi:ss') FROM
snowflakesales
 
SELECT TO_DATETIME('2009.03.03 01:01:00', 'yyyy.mm.dd hh:mi:ss') FROM
snowflakesales

Few more "secret" functions:

Aggregate Functions
FIRST - selects the first non-null returned value of the expression argument.
FIRST_PERIOD - selects the first returned value of the expression argument.
GROUPBYCOLUMN - Used only in Repository.
GROUPBYLEVEL - Used only in Repository.
LAST - selects the last non-null returned value of the expression.
LAST_PERIOD - selects the last returned value of the expression.
STDDEV_POP - returns the standard deviation for a set of values using the
computational formula for population variance and standard deviation.
 
Math Functions
EXTRACTBIT - retrieves a bit at a particular position in an integer. It returns an integer
of either 0 or 1 corresponding to the position of the bit.
 
 Conversion Functions
CHOOSE - returns the first item in the list that the user has permission to see.
INDEXCOL - exists in Admin. Tool. Can use external information to return the appropriate column for the logged-in user to see.

Database Functions
EVALUATE_ANALYTIC - exists in Admin. Tool. This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.
EVALUATE_PREDICATE - exists in Admin. Tool. Passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for functions with a return type of Boolean.

Hierarchy Navigation Functions
IDOF -
ISANCESTOR
ISCHILD
ISDESCENDANT
ISLEAF
ISPARENT
ISROOT
PARENT


No comments:

Post a Comment