Using Oracle Business Intelligence for Oracle Taleo Enterprise Edition (2023)

The calendar date/timefunctions manipulate data of the data types DATE and DATETIME based on a calendar year. You must select thesefunctions with another column; they cannot be selected alone.

CURRENT_DATE

This function returns thecurrent date. The date is determined by the system in which the OracleBI Server is running.

Syntax

CURRENT_DATE

Example:

TIMESTAMPDIFF(SQL_TSI_DAY, "RequisitionDates"."First Fully Approved Date", CURRENT_DATE)

This willreturn the days between the First Fully Approved Date and today.

CURRENT_TIME

This function returns thecurrent time. The time is determined by the system in which the OracleBI Server is running.

Syntax

CURRENT_TIME(integer)

Where:

integer is any integer representingthe number of digits of precision with which to display the fractionalsecond. The argument is optional; the function returns the defaultprecision when no argument is specified.

CURRENT_TIMESTAMP

This function returnsthe current date/timestamp. The timestamp is determined by the systemin which the Oracle BI Server is running.

Syntax

Where:

integer is any integer representing the number of digits of precision withwhich to display the fractional second. The argument is optional;the function returns the default precision when no argument is specified.

DAY_OF_QUARTER

This function returnsa number (between 1 and 92) corresponding to the day of the quarterfor the specified date.

Syntax

DAY_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

Example:

DAY_OF_QUARTER(“Requisition Dates”.”FirstFully Approved Date”)

This will return the corresponding dayof the quarter for the First Fully Approved Date.

DAYNAME

This function returns the nameof the day of the week for a specified date.

Syntax

DAYNAME(dateExpr) 

Where:

DAYOFMONTH

This function returns thenumber corresponding to the day of the month for a specified date.

Syntax

DAYOFMONTH(dateExpr) 

Where:

dateExpr is any expressionthat evaluates to a date.

DAYOFWEEK

This function returns a numberbetween 1 and 7 corresponding to the day of the week, Sunday throughSaturday, for a specified date. For example, the number 1 correspondsto Sunday, and the number 7 corresponds to Saturday.

Syntax

DAYOFWEEK(dateExpr) 

Where:

dateExpr is any expression that evaluates toa date.

DAYOFYEAR

This function returns the number(between 1 and 366) corresponding to the day of the year for a specifieddate.

Syntax

DAYOFYEAR(dateExpr) 

Where:

dateExpr is any expressionthat evaluates to a date.

HOUR

This function returns a number (between0 and 23) corresponding to the hour for a specified time. For example,0 corresponds to 12 a.m. and 23 corresponds to 11 p.m.

Syntax

HOUR(timeExpr) 

Where:

timeExpr is any expression that evaluatesto a time.

MINUTE

This function returns a number(between 0 and 59) corresponding to the minute for a specified time.

Syntax

MINUTE(timeExpr) 

Where:

timeExpr is any expressionthat evaluates to a time.

MONTH

This function returns the number(between 1 and 12) corresponding to the month for a specified date.

Syntax

MONTH(dateExpr) 

Where:

dateExpr is any expressionthat evaluates to a date.

MONTH_OF_QUARTER

This function returnsthe number (between 1 and 3) corresponding to the month in the quarterfor a specified date.

Syntax

MONTH_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression thatevaluates to a date.

MONTHNAME

This function returns the nameof the month for a specified date.

Syntax

MONTHNAME(dateExpr) 

Where:

dateExpr is any expression that evaluates to a date.

NOW

This function returns the currenttimestamp. The NOW function is equivalent to the CURRENT_TIMESTAMP function.

Syntax

NOW()

QUARTER_OF_YEAR

This function returnsthe number (between 1 and 4) corresponding to the quarter of the yearfor a specified date.

Syntax

QUARTER_OF_YEAR(dateExpr)

Where:

dateExpr is any expressionthat evaluates to a date.

SECOND

This function returns the number(between 0 and 59) corresponding to the seconds for a specified time.

Syntax

SECOND(timeExpr) 

Where:

timeExpr is any expressionthat evaluates to a time.

TIMESTAMPADD

This function adds a specifiednumber of intervals to a specified timestamp, and returns a singletimestamp.

In the simplest scenario, this function adds thespecified integer value to the appropriate component of the timestamp,based on the interval. Adding a week translates to adding seven days,and adding a quarter translates to adding three months. A negativeinteger value results in a subtraction (such as going back in time).

An overflow of the specified component (such as more than 60seconds, 24 hours, 12 months, and so on) necessitates adding an appropriateamount to the next component. For example, when adding to the daycomponent of a timestamp, this function considers overflow and takesinto account the number of days in a particular month (including leapyears when February has 29 days).

When adding to the monthcomponent of a timestamp, this function verifies that the resultingtimestamp has enough days for the day component. For example, adding1 month to 2000-05-31 does not result in 2000-06-31 because June doesnot have 31 days. This function reduces the day component to the lastday of the month, 2000-06-30 in this example.

A similar issuearises when adding to the year component of a timestamp having a monthcomponent of February and a day component of 29 (that is, last dayof February in a leap year). If the resulting timestamp does not fallon a leap year, the function reduces the day component to 28.

These actions conform to the behavior of Microsoft SQL Serverand the native OCI interface for Oracle Database.

Syntax

TIMESTAMPADD(interval, intExpr, timestamp) 

Where:

intExpr is any expression that evaluatesto an integer value.

timestamp is any validtimestamp. This value is used as the base in the calculation.

A null integer expression or a null timestamp passed to thisfunction results in a null return value.

Example:

TIMESTAMPADD(SQL_TSI_DAY, 7, CURRENT_DATE)

This will returna value of one week from today.

TIMESTAMPDIFF

This function returns thetotal number of specified intervals between two timestamps.

This function first determines the timestamp component that correspondsto the specified interval parameter, and then looks at the higherorder components of both timestamps to calculate the total numberof intervals for each timestamp. For example, if the specified intervalcorresponds to the month component, the function calculates the totalnumber of months for each timestamp by adding the month componentand twelve times the year component. Then the function subtracts thefirst timestamp's total number of intervals from the second timestamp'stotal number of intervals.

Note: This section describes the TIMESTAMPDIFF behavior when the function is calculated inthe Oracle BI Server. If this function is calculated in the data source,then the result might be different from the behavior described inthis section. If the TIMESTAMPDIFF function resultis different from the desired result, then you can disable TIMESTAMP_DIFF_SUPPORTED in the Features tab for the databaseobject in the Administration Tool to ensure that the function is calculatedin the Oracle BI Server. However, making this change might adverselyaffect performance.

The TIMESTAMPDIFF functionrounds up to the next integer whenever fractional intervals representa crossing of an interval boundary. For example, the difference inyears between 1999-12-31 and 2000-01-01 is one year because the fractionalyear represents a crossing from one year to the next (such as 1999to 2000). By contrast, the difference between 1999-01-01 and 1999-12-31is zero years because the fractional interval falls entirely withina particular year (that is, 1999). Microsoft SQL Server exhibits thesame rounding behavior, but IBM DB2 does not; it always rounds down.

When calculating the difference in weeks, the function calculatesthe difference in days and divides by seven before rounding. Additionally,the function takes into account how the parameter FIRST_DAY_OF_THE_WEEK has been configured in the NQSConfig.INI file. For example, withSunday as the start of the week, the difference in weeks between 2000-07-06(a Thursday) and 2000-07-10 (the following Monday) results in a valueof 1 week. With Tuesday as the start of the week, however, the functionwould return zero weeks since the fractional interval falls entirelywithin a particular week. When calculating the difference in quarters,the function calculates the difference in months and divides by threebefore rounding.

The Oracle BI Server pushes down the TIMESTAMPADD and TIMESTAMPDIFF functionsto Microsoft SQL Server, Oracle Database, IBM DB2, and ODBC databasesby default.

Syntax

TIMESTAMPDIFF(interval, timestamp1, timestamp2) 

Where:

timestamp1 and timestamp2 are any valid timestamps.

A null timestamp parameter passed to this function results ina null return value.

Example:

TIMESTAMPDIFF(SQL_TSI_DAY,"Requisition Dates"."First Sourced Date", "Requisition Dates"."LatestFilled Date")

This will return the days between the First SourcedDate and the Latest Filled Date.

WEEK_OF_QUARTER

This function returnsa number (between 1 and 13) corresponding to the week of the quarterfor the specified date.

Syntax

WEEK_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

WEEK_OF_YEAR

This function returns anumber (between 1 and 53) corresponding to the week of the year forthe specified date.

Syntax

WEEK_OF_YEAR(dateExpr)

Where:

dateExpr is any expressionthat evaluates to a date.

YEAR

This function returns the year forthe specified date.

Syntax

YEAR(dateExpr) 

Where:

dateExpr is any expressionthat evaluates to a date.

Example:

YEAR("RequisitionDates"."First Sourced Date")

This will give you the year ofthe First Sourced Date.