# Calendar Date/Time Functions

The calendar date/time
functions manipulate data of the data types `DATE`

and `DATETIME`

based on a calendar year. You must select these
functions with another column; they cannot be selected alone.

## CURRENT_DATE

This function returns the current date. The date is determined by the system in which the Oracle BI Server is running.

**Syntax**

`CURRENT_DATE`

**Example:**

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

This will return the days between the First Fully Approved Date and today.

## CURRENT_TIME

This function returns the current time. The time is determined by the system in which the Oracle BI Server is running.

**Syntax **

`CURRENT_TIME(integer)`

Where:

`integer`

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

## CURRENT_TIMESTAMP

This function returns the current date/timestamp. The timestamp is determined by the system in which the Oracle BI Server is running.

**Syntax**

Where:

`integer`

is any integer representing the number of digits of precision with
which 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 returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date.

**Syntax**

`DAY_OF_QUARTER(dateExpr)`

Where:

`dateExpr`

is any expression that evaluates to a date.

**Example:**

DAY_OF_QUARTER(“Requisition Dates”.”First Fully Approved Date”)

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

## DAYNAME

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

**Syntax**

`DAYNAME(dateExpr) `

Where:

`dateExpr`

is any expression that evaluates to a date.## DAYOFMONTH

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

**Syntax**

`DAYOFMONTH(dateExpr) `

Where:

`dateExpr`

is any expression
that evaluates to a date.

## DAYOFWEEK

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

**Syntax**

`DAYOFWEEK(dateExpr) `

Where:

`dateExpr`

is any expression that evaluates to
a date.

## DAYOFYEAR

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

**Syntax**

`DAYOFYEAR(dateExpr) `

Where:

`dateExpr`

is any expression
that evaluates to a date.

## HOUR

This function returns a number (between 0 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 evaluates
to 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 expression
that 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 expression
that evaluates to a date.

## MONTH_OF_QUARTER

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

**Syntax**

MONTH_OF_QUARTER(dateExpr)

Where:

`dateExpr`

is any expression that
evaluates to a date.

## MONTHNAME

This function returns the name of the month for a specified date.

**Syntax**

`MONTHNAME(dateExpr) `

Where:

`dateExpr`

is any expression that evaluates to a date.

## NOW

This function returns the current
timestamp. The `NOW`

function is equivalent to the `CURRENT_TIMESTAMP`

function.

**Syntax**

`NOW()`

## QUARTER_OF_YEAR

This function returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date.

**Syntax**

`QUARTER_OF_YEAR(dateExpr)`

Where:

`dateExpr`

is any expression
that 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 expression
that evaluates to a time.

## TIMESTAMPADD

This function adds a specified number of intervals to a specified timestamp, and returns a single timestamp.

In the simplest scenario, this function adds the specified 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 negative integer value results in a subtraction (such as going back in time).

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

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

A similar issue arises when adding to the year component of a timestamp having a month component of February and a day component of 29 (that is, last day of February in a leap year). If the resulting timestamp does not fall on a leap year, the function reduces the day component to 28.

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

**Syntax**

`TIMESTAMPADD(interval, intExpr, timestamp) `

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`

`intExpr`

is any expression that evaluates
to an integer value.

`timestamp`

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

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

**Example:**

TIMESTAMPADD(SQL_TSI_DAY, 7, CURRENT_DATE)

This will return a value of one week from today.

## TIMESTAMPDIFF

This function returns the total number of specified intervals between two timestamps.

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

`TIMESTAMPDIFF`

behavior when the function is calculated in
the Oracle BI Server. If this function is calculated in the data source,
then the result might be different from the behavior described in
this section. If the `TIMESTAMPDIFF`

function result
is different from the desired result, then you can disable `TIMESTAMP_DIFF_SUPPORTED`

in the Features tab for the database
object in the Administration Tool to ensure that the function is calculated
in the Oracle BI Server. However, making this change might adversely
affect performance.The `TIMESTAMPDIFF`

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

When calculating the difference in weeks, the function calculates
the 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, with
Sunday 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 value
of 1 week. With Tuesday as the start of the week, however, the function
would return zero weeks since the fractional interval falls entirely
within a particular week. When calculating the difference in quarters,
the function calculates the difference in months and divides by three
before rounding.

The Oracle BI Server pushes down the `TIMESTAMPADD`

and `TIMESTAMPDIFF`

functions
to Microsoft SQL Server, Oracle Database, IBM DB2, and ODBC databases
by default.

**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.

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

**Example:**

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

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

## WEEK_OF_QUARTER

This function returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date.

**Syntax**

`WEEK_OF_QUARTER(dateExpr)`

Where:

`dateExpr`

is any expression that evaluates to a date.

## WEEK_OF_YEAR

This function returns a number (between 1 and 53) corresponding to the week of the year for the specified date.

**Syntax **

`WEEK_OF_YEAR(dateExpr)`

Where:

`dateExpr`

is any expression
that evaluates to a date.

## YEAR

This function returns the year for the specified date.

**Syntax**

`YEAR(dateExpr) `

Where:

`dateExpr`

is any expression
that evaluates to a date.

**Example:**

YEAR("Requisition Dates"."First Sourced Date")

This will give you the year of the First Sourced Date.