Date and Time Functions

Date and time functions manipulate data based on DATE and DATETIME.

Function	Example	Description	Syntax
CURRENT_Date	CURRENT_DATE	Returns the current date. The date is determined by the system in which the Oracle BI is running.	CURRENT_DATE
CURRENT_TIME	CURRENT_TIME(3)	Returns the current time to the specified number of digits of precision, for example: HH:MM:SS.SSS If no argument is specified, the function returns the default precision.	CURRENT_TIME(expr)
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP(3)	Returns the current date/timestamp to the specified number of digits of precision.	CURRENT_TIMESTAMP(expr)
DAYNAME	DAYNAME(Order_Date)	Returns the name of the day of the week for a specified date expression.	DAYNAME(expr)
DAYOFMONTH	DAYOFMONTH(Order_Date)	Returns the number corresponding to the day of the month for a specified date expression.	DAYOFMONTH(expr)
DAYOFWEEK	DAYOFWEEK(Order_Date)	Returns a number between 1 and 7 corresponding to the day of the week for a specified date expression. For example, 1 always corresponds to Sunday, 2 corresponds to Monday, and so on through to Saturday which returns 7.	DAYOFWEEK(expr)
DAYOFYEAR	DAYOFYEAR(Order_Date)	Returns the number (between 1 and 366) corresponding to the day of the year for a specified date expression.	DAYOFYEAR(expr)
DAY_OF_QUARTER	DAY_OF_QUARTER(Order_Date)	Returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date expression.	DAY_OF_QUARTER(expr)
HOUR	HOUR(Order_Time)	Returns a number (between 0 and 23) corresponding to the hour for a specified time expression. For example, 0 corresponds to 12 a.m. and 23 corresponds to 11 p.m.	HOUR(expr)
MINUTE	MINUTE(Order_Time)	Returns a number (between 0 and 59) corresponding to the minute for a specified time expression.	MINUTE(expr)
MONTH	MONTH(Order_Time)	Returns the number (between 1 and 12) corresponding to the month for a specified date expression.	MONTH(expr)
MONTHNAME	MONTHNAME(Order_Time)	Returns the name of the month for a specified date expression.	MONTHNAME(expr)
MONTH_OF_QUARTER	MONTH_OF_QUARTER(Order_Date)	Returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date expression.	MONTH_OF_QUARTER(expr)
NOW	NOW()	Returns the current timestamp. The NOW function is equivalent to the CURRENT_TIMESTAMP function.	NOW()
QUARTER_OF_YEAR	QUARTER_OF_YEAR(Order_Date)	Returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date expression.	QUARTER_OF_YEAR(expr)
SECOND	SECOND(Order_Time)	Returns the number (between 0 and 59) corresponding to the seconds for a specified time expression.	SECOND(expr)
TIMESTAMPADD	TIMESTAMPADD(SQL_TSI_MONTH, 12,Time."Order Date")	Adds a specified number of intervals to a timestamp, and returns a single timestamp. Interval options 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	TIMESTAMPADD(interval, expr, timestamp)
TIMESTAMPDIFF	TIMESTAMPDIFF(SQL_TSI_MONTH, Time."Order Date",CURRENT_DATE)	Returns the total number of specified intervals between two timestamps. Use the same intervals as TIMESTAMPADD.	TIMESTAMPDIFF(interval, expr, timestamp2)
WEEK_OF_QUARTER	WEEK_OF_QUARTER(Order_Date)	Returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date expression.	WEEK_OF_QUARTER(expr)
WEEK_OF_YEAR	WEEK_OF_YEAR(Order_Date)	Returns a number (between 1 and 53) corresponding to the week of the year for the specified date expression.	WEEK_OF_YEAR(expr)
YEAR	YEAR(Order_Date)	Returns the year for the specified date expression.	YEAR(expr)