An Oracle blog about BI Publisher

Date Parameters

Another document slippage, I was a little embarassed this week to send someone to our documentation asking them to rtfm first. Only to find we had not documented the feature they were interested in, well, we had, it was just not there ... profuse apologies ensued.

The feature in question was our date parameter support we have the ability to default dates into parameter values and not just SYSDATE either - we can get quite sophisticated ... read on

Date Parameter - passes a date parameter. If you select a Parameter Type of Date, the Data

Type automatically defaults to Date. Enter the following:

Examples of these masks include:

Date Mask




yyyy.MM.dd G 'at' HH:mm:ss z

2007.01.01 AD at 12:08:56 PDT

EEE, MMM d, ''yy

Wed, Jan 1, '07

h:mm a

12:08 PM

hh 'o''clock' a, zzzz

12 o'clock PM, Pacific Daylight Time

K:mm a, z

0:08 PM, PDT

yyyyy.MMMMM.dd GGG hh:mm aaa

02001.July.04 AD 12:08 PM

EEE, d MMM yyyy HH:mm:ss Z

Wed, 4 Jul 2001 12:08:56 -0700

  • Default Dates - this can either be a fixed date, must match the date format mask or there are the following supported date functions:


Todays Date


First day of the current month


Last day of the current month


First day of the current year


Last day of the current year

These functions also support simple addition and subtraction operators so SYSDATE()-1 would be yesterday痴 date. When used these date functions need to be surrounded with curly brace followed by a dollar sign i.e. {$SYSDATE()$} or {$SYSDATE()-1$}

  • Date From and Date To ? this will limit the date picker UI to dates within the range specified. Currently these fields can not accept the date functions documented above as input. Note dates must conform to the date mask entered above.

When the report is executed the date string will be passed to the extraction engine in the format specified in the format mask.

So, for those of you looking for date parameter info ... its here and it will make it to the docs soon.


Join the discussion

Comments ( 10 )
  • Preeti Sharma Monday, July 21, 2008
    Hi, I want to know, if we need to get current month then which function we can use in Bi Publisher.
    Hi Preeti
    There are several ways to do it - take a look at the user docs for more information.
  • Cliff Salyer Friday, April 24, 2009
    It seems to me the most common thing within the ERP is to use the FND_STANDARD_DATE as a report parameter value set and passing that into BIP. Are there any EXPLICIT examples of that. Comparing a create date for example to a date passed as a parameter usnig the FND_STANDARD_DATE value set? Creation Date as an example would apply equally to any date field in a table.
  • Chris Nance Wednesday, July 8, 2009
    When will ADD_MONTHS, TRUNC, and other date functionality be available for use with default date parameters? Can you use ADD_MONTHS with FIRST_DAY_OF_MONTH for example?
  • Jorge Monday, September 28, 2009
    Hi, is it possible to include any type of validation for the parameter?
    Because, if I use DATE and instead of date I insert an common string like "chic" the report allows me to navigate to another parameter field even that I know that the report will fail.
    Thanks in advance
  • Mary Ann Ryan Tuesday, January 12, 2010

    Do you think it's

  • Cassandra Gilmour Sunday, June 6, 2010
    I am after a response to Chris Nance's post.
    When will ADD_MONTHS, TRUNC, and other date functionality be available for use with default date parameters? Can you use ADD_MONTHS with FIRST_DAY_OF_MONTH for example?
    I am trying to default my input start date parameter for a monthly report to the first day of LAST month. Likewise, I need to default an input end date parameter to the last day of last month.
  • pallavi Wednesday, July 21, 2010

    one more doubt...in bi publisher
    i want my parameter value to be displayed in report output..
    i m using
    in rtf template....
    still i m not getting the result.
    if i use
    then i m getting correct value in Start_date ...but Last_date also i m getting Start_date value.
    my xml column is Cal_Datetime corresponding to both the parameters.....
    plz reply...
  • Mithila Wednesday, October 27, 2010
    hi ,
    i need to run a report for activities displayin the planned date of type appintment and also the immediate next appointment's planned date.
    can anyone help me with this ? how to display the next appointment's planned date ?
  • guest Wednesday, December 7, 2011

    I just used your default parameter value tip; {$SYSDATE()-1$}


  • Ganesh Monday, October 7, 2013

    Hi,i want to get the previous month's start date & end date of the current date & Previous start date of the week & end date of the week of current date.

    i have used <?xdoxslt:sysdate()-7?> this is working but not correctly say i run today then it will give date as 131000.This is not the correct answer right?i want 130930 date.

    Please reply for this.Need help from all.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.