Oracle^{® }Business Intelligence Server Administration Guide > Oracle BI Server SQL Reference > SQL Reference >
Calendar Date/Time Functions
The calendar date/time functions manipulate data of the data types DATE and DATETIME. Current_Date
Returns the current date. The date is determined by the system in which the Oracle BI Server is running. Syntax: CURRENT_DATE
Current_Time
Returns the current time. The time is determined by the system in which the Oracle BI Server is running. 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 the Oracle BI Server is running. 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 day of the week 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, Sunday through Saturday, for a specified date. For example, 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 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. 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, integerexpression, timestampexpression)
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. 
timestamp_expression 
The timestamp used as the base in the calculation. 
A null integerexpression or a null timestampexpression passed to this function will result in a null return value. In the simplest scenario, this function merely adds the specified integer value (integerexpression) 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 20000531 does not result in 20000631 because June does not have 31 days. This function reduces the day component to the last day of the month, 20000630 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's SQL Server and Oracle's native OCI interface. The following queries are examples of the TimestampAdd function and its results: The following query asks for the resulting timestamp when 3 days are added to 20000227 14:30:00. Since February, 2000 is a leap year, the query returns a single timestamp of 20000301 14:30:00. Select TimestampAdd(SQL_TSI_DAY, 3,
TIMESTAMP'20000227 14:30:00')
From Employee where employeeid = 2;
The following query asks for the resulting timestamp when 7 months are added to 19990731 0:0:0. The query returns a single timestamp of 20000229 00:00:00. Notice the reduction of day component to 29 because of the shorter month of February. Select TimestampAdd(SQL_TSI_MONTH, 7,
TIMESTAMP'19990731 00:00:00')
From Employee where employeeid = 2;
The following query asks for the resulting timestamp when 25 minutes are added to 20000731 23:35:00. The query returns a single timestamp of 20000801 00:00:00. Notice the propagation of overflow through the month component. Select TimestampAdd(SQL_TSI_MINUTE, 25,
TIMESTAMP'20000731 23:35:00')
From Employee where employeeid = 2;
CAUTION: The TIMESTAMPADD function is turned on by default for Microsoft SQL Server, ODBC, IBM DB2, and Oracle databases. Because DB2 and Oracle semantics do not fully support this function, the answers from this function might not match exactly with what the Oracle BI Server computes.
TimeStampDiff
The TimestampDiff function returns the total number of specified intervals between two timestamps. Syntax: TimestampDiff (interval, timestampexpression1, timestampexpression2)
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 used in the function. 
timestamp_expression2 
The first timestamp used in the function. 
A null timestampexpression 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 19991231 and 20000101 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 19990101 and 19991231 is zero years because the fractional interval falls entirely within a particular year (that is, 1999). Microsoft's SQL Server exhibits the same rounding behavior. IBM DB2 always rounds down. Oracle does not implement a generalized timestamp difference function. 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 Oracle BI Administrator has configured the start of a new week in the NQSConfig.INI file using the parameter FIRST_DAY_OF_THE_WEEK (defaults to Sunday). For example, with Sunday as the start of the week, the difference in weeks between 20000706 (a Thursday) and 20000710 (the following Monday) results in a value of one 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. IBM DB2 provides a generalized timestamp difference function (TIMESTAMPDIFF) but it simplifies the calculation by always assuming a 365day year, 52week year, and 30day month. TimestampDiff Function and Results Example
The following query asks for a difference in days between timestamps 19980731 23:35:00 and 20000401 14:24:00. It returns a value of 610. Notice that the leap year in 2000 results in an additional day. Select TimestampDIFF(SQL_TSI_DAY, TIMESTAMP'19980731 23:35:00', TIMESTAMP'20000401 14:24:00') From Employee where employeeid = 2;
CAUTION: The TIMESTAMPDIFF function is turned on by default for Microsoft SQL Server, ODBC, IBM DB2, and Oracle databases. Because DB2 and Oracle semantics do not fully support this function, the answers from this function might not match exactly with what the Oracle BI Server computes.
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. 
