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)`

Tips on Using Date Functions

Here're some tips on getting the best results when using date functions in your workbooks to calculate fiscal periods, convert strings to dates, show a date and time in a specific time zone, and calculate differences over time.

Calculate fiscal year, quarter and month:

Subject areas typically have a time dimension, so you can use the pre-configured periods such as fiscal quarter, fiscal month, and fiscal week. If you only have a date field, you can calculate these periods yourself using date functions.

This example calculates fiscal quarter:

'FY'||cast(YEAR(TIMESTAMPADD(SQL_TSI_MONTH, 7,<date field>)) as char)||'Q'||cast(QUARTER(TIMESTAMPADD(SQL_TSI_MONTH, 7,<same date field>)) as char)

This example calculates fiscal year and month:

'FY'||cast(YEAR(TIMESTAMPADD(SQL_TSI_MONTH, 7,<date field>)) as char)||cast(MONTHNAME(<same date field>) as char)

Notes:

|| concatenates values.
cast (xxx(date) as char) extracts a portion of a date field.
TIMESTAMPADD adds (or substracts) periods from a date. This example uses the parameter value SQL_TSI_MONTH to add months.

The calculations above work well in tables but when used in filters the spacing between returned values might look odd. In this case, use a cleaner but more complex calculation based on these examples:

'FY'||evaluate ('to_char(%1,%2)'as char,(EVALUATE('add_months(%1,%2)' as date,<date field>,7)),'YY')||'-Q'||evaluate ('to_char(%1,%2)'as char,(EVALUATE('add_months(%1,%2)' as date,<same date field>,7)),'Q')

'FY'||evaluate ('to_char(%1,%2)'as char,(EVALUATE('add_months(%1,%2)' as date,<date field>,7)),'YY')||'-'||evaluate ('to_char(%1,%2)'as char,<same date field>,'MON')

Convert a string to a date:

Oracle Analytics profiles your data and provides enrichment recommendations for extracting and converting dates. If you need to convert a string to a date yourself, use these tips.

In the workbook designer, navigate in the data pane to the column that you wish to convert, then right-click and select Convert to Date. Configure the date then click Add Step.

Description of the illustration convert-string-date.png
In the workbook designer, navigate in the data pane to the column that you wish to convert, then right-click and select Edit. Configure the date then click Add Step.

Description of the illustration convert-string-date-2.png

In the text box containing the name of your column in blue, insert To_DateTime( before the column name, then add the date format you want to use after the column name, followed by ), then click Add Step.

Description of the illustration convert-string-date-3.png

When you use the Edit option to create your date-conversion function, you can also use Allow_Variable_Digits to process column values with one-digit or two digit numbers, and Null_On_Error arguments to discard rows that don't match the pattern required. See also REPLACE and CAST for alternative ways to process data that doesn't match the pattern required.

As an alterntive to using To_DateTime(), use CAST() to change a value's data type. For example, CAST(SalesDate AS DATE).

Additionally, use the Replace option. In the workbook designer, navigate in the data pane to the column that you wish to convert, then right-click and select Replace. Configure the replacement type you want, then click Add Step.

Show a date and time field in a specific timezone:

Use the NEW_TIME function in the database in conjunction with EVALUATE. For example:

evaluate ('new_time(%1,%2,%3)' as char, "DV - Pipeline and Forecast"."Opportunity"."Last Updated Date",'GMT','AST')

Make sure that the date field includes the time component.

Calculate difference over time:

To show the difference in number and percentage between now and yesterday create calculations based on these examples:

Calculate a measure until yesterday: filter (Measure using date <= TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))
Calculate the difference minus: Measure - filter (Measure using date <= TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))
Calculate the difference percentage: 1-(filter (Measure using date <= TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))/Measure display as percentage

Notes:

These examples use SQL_TSI_DAY, however you can alternatively use WEEK and MONTH week over week and month over month.
These examples work with a normal or natural calendar. If you need fiscal months and quarters, use repository variables in your calculations instead of TIMESTAMPADD. Alternatively, combine this with the Calculate fiscal year, quarter and month tip.
Some subject areas include pre-calculated Prior X measures like PY (prior year), PQ (prior quarter) that you can use to calculate differences over time.
Alternatively, you can use the TIME SERIES functions: AGO, PERIODROLLING and TODATE. See Time Series Functions.