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.



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. Tim

Posted by Preeti Sharma on July 20, 2008 at 07:59 PM MDT #

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.

Posted by Cliff Salyer on April 24, 2009 at 02:32 AM MDT #

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?

Posted by Chris Nance on July 08, 2009 at 02:07 AM MDT #

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

Posted by Jorge on September 28, 2009 at 01:43 AM MDT #

Do you think it's

Posted by Mary Ann Ryan on January 12, 2010 at 01:10 PM MST #

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. Thanks.

Posted by Cassandra Gilmour on June 06, 2010 at 12:46 PM MDT #

thanks......alot one more bi publisher i want my parameter value to be displayed in report output.. i m using and 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...

Posted by pallavi on July 20, 2010 at 11:01 PM MDT #

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 ? thanks, Mithila

Posted by Mithila on October 26, 2010 at 10:39 PM MDT #

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


Posted by guest on December 07, 2011 at 02:05 AM MST #

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.

Posted by Ganesh on October 07, 2013 at 07:14 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!


« July 2016