Print      Open PDF Version of Online Help


Previous Topic

Next Topic

Calendar Date/Time Functions

The calendar date/time functions manipulate data in data types for date, time and timestamp. (The timestamp data type is a combination of date and time.)

All date-functions are ISO 8601 compliant. This particularly affects functions such as Week_Of_Year and Week_Of_Quarter where the initial week of the year is the first full week that includes a Thursday. For detailed information on how date functions are calculated, consult the ISO 8601 standard.

Current_Date

Returns the current date. The date is determined by the system in which Oracle CRM On Demand Answers is running. The value does not contain a time component.

Syntax:

CURRENT_DATE

Current_Time

Returns the current time. The time is determined by the system in which Oracle CRM On Demand Answers is running. The value does not contain a date component.

Note: This function obtains the current time at the time the report is run. Using this function with an analytics subject area prevents the report from caching, which decreases performance.

Syntax:

CURRENT_TIME (n)

where:

n

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

Returns the current date/timestamp. The timestamp is determined by the system in which Oracle CRM On Demand Answers is running.

Note: This function obtains the current time at the time the report is run. Using this function with an analytics subject area prevents the report from caching, which decreases performance.

Syntax:

CURRENT_TIMESTAMP (n)

where:

n

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

Returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date.

Syntax:

DAY_OF_QUARTER (date_expression)

where:

date_expression

Any expression that evaluates to a date.

DayName

Returns the name of the day of the week (in English) for a specified date.

Syntax:

DAYNAME (date_expression)

where:

date_expression

Any expression that evaluates to a date.

DayOfMonth

Returns the number corresponding to the day of the month for a specified date.

Syntax:

DAYOFMONTH (date_expression)

where:

date_expression

Any expression that evaluates to a date.

DayOfWeek

Returns a number between 1 and 7, corresponding to the day of the week, for a specified date. The number 1 corresponds to Sunday, and the number 7 corresponds to Saturday.

Syntax:

DAYOFWEEK (date_expression)

where:

date_expression

Any expression that evaluates to a date.

DayOfYear

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

Syntax:

DAYOFYEAR (date_expression)

where:

date_expression

Any expression that evaluates to a date.

Hour

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 (time_expression)

where:

time_expression

Any expression that evaluates to a time.

Minute

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

Syntax:

MINUTE (time_expression)

where:

time_expression

Any expression that evaluates to a time.

Month

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

Syntax:

MONTH (date_expression)

where:

date_expression

Any expression that evaluates to a date.

Month_Of_Quarter

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

Syntax:

MONTH_OF_QUARTER (date_expression)

where:

date_expression

Any expression that evaluates to a date.

MonthName

Returns the name of the month (in English) for a specified date.

Syntax:

MONTHNAME (date_expression)

where:

date_expression

Any expression that evaluates to a date.

Now

Returns the current timestamp. The NOW function is equivalent to the CURRENT_TIMESTAMP function.

Note: This function obtains the current time at the time the report is run. Using this function with an analytics subject area prevents the report from caching, which decreases performance.

Syntax:

NOW ()

Quarter_Of_Year

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

Syntax:

QUARTER_OF_YEAR (date_expression)

where:

date_expression

Any expression that evaluates to a date.

Second

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

Syntax:

SECOND (time_expression)

where:

time_expression

Any expression that evaluates to a time.

TimestampAdd

The TimestampAdd function adds a specified number of intervals to a specified timestamp. A single timestamp is returned.

Syntax:

TimestampAdd (interval, integer_expression, timestamp_expression)

where:

interval

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

integer_expression

Any expression that evaluates to an integer. This is the number of intervals to add.

timestamp_expression

The timestamp used as the base in the calculation.

A null integer-expression or a null timestamp-expression passed to this function will result in a null return value.

In the simplest scenario, this function merely adds the specified integer value (integer_expression) 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 (going back in time).

An overflow of the specified component (such as more than 60 seconds, 24 hours, twelve 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 a sufficient number of 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.

The following are examples of the TimestampAdd function:

The following code example asks for the resulting timestamp when 3 days are added to 2000-02-27 14:30:00. Because February, 2000 is a leap year, this returns a single timestamp of 2000-03-01 14:30:00.

TimestampAdd(SQL_TSI_DAY, 3, TIMESTAMP‘2000-02-27 14:30:00’)

The following code example asks for the resulting timestamp when 7 months are added to 1999-07-31 0:0:0. This returns a single timestamp of 2000-02-29 00:00:00. Notice the reduction of day component to 29 because of the shorter month of February.

TimestampAdd(SQL_TSI_MONTH, 7, TIMESTAMP‘1999-07-31 00:00:00’)

The following code example asks for the resulting timestamp when 25 minutes are added to 2000-07-31 23:35:00. This returns a single timestamp of 2000-08-01 00:00:00. Notice the propagation of overflow through the month component.

TimestampAdd(SQL_TSI_MINUTE, 25, TIMESTAMP‘2000-07-31 23:35:00’)

TimeStampDiff

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

Syntax:

TimestampDiff (interval, timestamp_expression1, timestamp_expression2)

where:

interval

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

timestamp_expression1

The timestamp to subtract from the second timestamp.

timestamp_expression2

The second timestamp. timestamp_expression1 is subtracted from this timestamp to determine the difference.

A null timestamp-expression parameter passed to this function will result in a null return value.

This function first determines the timestamp component that corresponds to the specified interval parameter. For example, SQL_TSI_DAY corresponds to the day component and SQL_TSI_MONTH corresponds to the month component.

The function 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.

Finally, the function subtracts the first timestamp’s total number of intervals from the second timestamp’s total number of intervals.

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 (that is, 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).

When calculating the difference in weeks, the function calculates the difference in days and divides by seven before rounding.

When calculating the difference in quarters, the function calculates the difference in months and divides by three before rounding.

TimestampDiff Function and Results Example

The following code example asks for a difference in days between timestamps 1998-07-31 23:35:00 and 2000-04-01 14:24:00. It returns a value of 610. Notice that the leap year in 2000 results in an additional day.

TimestampDIFF(SQL_TSI_DAY, TIMESTAMP‘1998-07-31 23:35:00’, TIMESTAMP‘2000-04-01 14:24:00’)

Week_Of_Quarter

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

Syntax:

WEEK_OF_QUARTER (date_expression)

where:

date_expression

Any expression that evaluates to a date.

Week_Of_Year

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

Syntax:

WEEK_OF_YEAR (date_expression)

where:

date_expression

Any expression that evaluates to a date.

Year

Returns the year for the specified date.

Syntax:

YEAR (date_expression)

where:

date_expression

Any expression that evaluates to a date.

Example:

YEAR (CURRENT_DATE)


Published 5/4/2012 Copyright © 2005, 2012, Oracle. All rights reserved. Legal Notices.