For Getting Age between two dates we use SQL Query as :
datediff(d, startdate, enddate) as Age
However default date format in OBIEE is Timestamp. So you need timestamp function (Available under Calendar/Date Function Heading) to get required result.
Syntax:
TIMESTAMPDIFF(interval, timestamp1, timestamp2)
Where:
interval is the specified interval. Valid values are:
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
timestamp1 and timestamp2 are any valid timestamps.
Example
For Number of Days between two days:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, “TO_DATE_COLUMN”)
For Number of Days till current date:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, CURRENT_DATE)
*You can also use this function for getting age difference for other time dimension attributes as week, quarter, month or year. All you need to do is change first attribute i.e 'interval' & other attribute's accordingly.
datediff(d, startdate, enddate) as Age
However default date format in OBIEE is Timestamp. So you need timestamp function (Available under Calendar/Date Function Heading) to get required result.
Syntax:
TIMESTAMPDIFF(interval, timestamp1, timestamp2)
Where:
interval is the specified interval. Valid values are:
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
timestamp1 and timestamp2 are any valid timestamps.
Example
For Number of Days between two days:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, “TO_DATE_COLUMN”)
For Number of Days till current date:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, CURRENT_DATE)
*You can also use this function for getting age difference for other time dimension attributes as week, quarter, month or year. All you need to do is change first attribute i.e 'interval' & other attribute's accordingly.
