X

An Oracle blog about BI Publisher

  • RTF
    September 8, 2008

Date Functions

One thing from the XSL 2.0 specification that we can not leverage (for various reasons) is the native date|time support. Its not that the Oracle XSLT engine does not support, more tha we are stuck on an earlier version for now. Dont ask, its a sore point and gets way too complicated too quickly.
We are currently working on some migration tools for Crystal to Publisher conversion - before you ask, no I dont have a date. Out of that work we have been building out some new functions in the template layer to handle various Crystal funkiness. Thats not to say date calculations are funky - we needed those anyway but definitely needed them for any potential Crystal migration. Leslie has 'lovingly' written up all of the XSL extensions here, I was almost moved to tears while reading them - nah, Im kidding, come on, its a tech doc! Sorry Leslie. Here, Im just highlighting the date related functions below.


SQL Statement or XSL ExpressionUsageDescription
to_date<?xdofx:to_date ( char [, fmt [, 'nlsparam']]
)
TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or
NVARCHAR2 datatype to a value of DATE datatype. Thefmt is a date format specifying the format ofchar. If you omit fmt, then char must
be in the default date format. If fmt is 'J', for
Julian, then char must be an integer.
sysdate()<?xdofx:sysdate()?>SYSDATE returns the current date and time. The datatype
of the returned value is DATE. The function requires no
arguments.
current_date()<?xdoxslt:current_date($_XDOLOCALE,
$_XDOTIMEZONE)?>

Example: <?xdoxslt:current_date('ja-JP',
'Asia/Tokyo')?>
Returns the current date in "yyyy-MM-dd" format in the
given locale and timezone. This function supports only the
Gregorian calendar.
current_time()<?xdoxslt:current_time($_XDOLOCALE,
$_XDOTIMEZONE)?>

Example:

<?xdoxslt:current_time('ja-JP', 'Asia/Tokyo')?>
Returns the current time in the given locale and
timezone. This function supports only the Gregorian
calendar.
date_diff<?xdoxslt:date_diff(‘y',
‘YYYY-MM-DD’, ‘YYYY-MM-DD’,
$_XDOLOCALE, $_XDOTIMEZONE)?>

This function provides a method to get the difference
between two dates in the given locale. The dates need to
be in "yyyy-MM-dd" format. This function supports only
the Gregorian calendar. The syntax is as follows:

<?xdoxslt:date_diff(‘format’,
‘YYYY-MM-DD’, ‘YYYY-MM-DD’,
$_XDOLOCALE, $_XDOTIMEZONE)?>

where

format is the time value for which the difference is to
be calculated. Valid values are :
  • y - for year
  • m - for month
  • w - for week
  • d - for day
  • h - for hour
  • mi - for minute
  • s - for seconds
  • ms - for milliseconds
Example:

<?xdoxslt:date_diff(‘y’,
‘2000-04-08’, ‘2001-05-01’,
$_XDOLOCALE, $_XDOTIMEZONE)?>

returns

1

Example:

<?xdoxslt:date_diff(‘m’,
‘2001-04-08’, ‘2000-02-01’,
$_XDOLOCALE, $_XDOTIMEZONE)?>

returns

-14

Example:

<?xdoxslt:date_diff(‘d’,
‘2006-04-08’, ‘2006-04-01’,
$_XDOLOCALE,
‘America/Los_Angeles’)?>

returns

-7
sec_diff
<?xdoxslt:sec_diff(‘2000-04-08T20:00:00’,
‘2000-04-08T21:00:00’, $_XDOLOCALE,
$_XDOTIMEZONE?>
This function provides a method to get the difference
between two dates in seconds in the given locale. The dates
need to be in "yyyy-MM-dd'T'HH:mm:ss". This function
supports only Gregorian calendar.

Example:

<?xdoxslt:sec_diff(‘2000-04-08T20:00:00’,
‘2000-04-08T21:00:00’, $_XDOLOCALE,
$_XDOTIMEZONE?>

returns

3600
get_day<?xdoxslt:get_day(‘2000-04-08’,
$_XDOLOCALE)?>
This function provides a method to get the day value of
a date in "yyyy-MM-dd" format in the given locale. This
function supports only the Gregorian calendar.

Example:

<?xdoxslt:get_day(‘2000-04-08’,
$_XDOLOCALE)?>

returns

8
get_month<?xdoxslt:get_month(‘2000-04-08’,
$_XDOLOCALE)?>
This function provides a method to get the month value
of a date in "yyyy-MM-dd" format in the given locale. This
function supports only the Gregorian calendar.

Example:

<?xdoxslt:get_month(‘2000-04-08’,
$_XDOLOCALE)?>

returns

4
get_year<?xdoxslt:get_year(‘2000-04-08’,
$_XDOLOCALE)?>
This function provides a method to get the year value
of a date in "yyyy-MM-dd" format in the given locale. This
function supports only the Gregorian calendar.

Example:

<?xdoxslt:get_year(‘2000-04-08’,
$_XDOLOCALE)?>

returns

2000
month_nameThis function provides a method to get the name of the
month in the given locale. This function supports only the
Gregorian calendar.

The syntax for this function is:

<?xdoxslt:month_name(month, [abbreviate?],
$_XDOLOCALE)?>

where

month is the numeric value of the month (Januany = 1)

and

[abbreviate?] is the value 0 for do not abbreviate or 1 for
abbreviate.

Example:

<?xdoxslt:month_name(12, 1,
‘fr-FR’)?>

returns

dec.

Example"

<?xdoxslt:month_name(1, 0, $_XDOLOCALE)?>

returns

January




Some neat functions in there, Im gonna be doing something with that date_diff function for sure. Not all good news thou, they are only currently for those of you on 10.1.3.3.2.

Join the discussion

Comments ( 9 )
  • Vetri Monday, September 8, 2008
    Hi Tim,
    Thanks for this info.
    here are few samples, which i documented, it might be helpful as well.
    http://winrichman.blogspot.com/2008/08/bi-publisher-xsl-xtended-continues.html
    http://winrichman.blogspot.com/2008/08/xslt-functions-continued.html
    http://winrichman.blogspot.com/2008/08/xslt-extended-functions.html
  • John Tuesday, March 17, 2009
    Hi Tim,
    Thanks for the post. I have a date in the format '14-MAR-09' in the XML.. I dont have the luxury to change the data source as it refers to C, Pro*C, Oracle Reports, Spawned programs.. wat not !!! Hence thought of formatting at the template layer. but the to_date function doesn't work with this format '14-MAR-09', How do I convert this to the canonical format so that I can format it with format_date or MS Word formatting...
    --Anto
  • kumar Tuesday, January 26, 2010
    Hi,
    I am using the .But xml is throwing error that method is not defined. Please help me to solve the issue.
    Thanks In Advance,
    Kumar
    Chetan.ora@gmail.com
  • guest Thursday, February 23, 2012

    Hi Tim

    Can you please tell me about adding number of days to current date. As in SQL we simply add (sysdate+no_of_days) and in result we get the new date. How can we achieve it in BI Publisher ...

    Regards


  • guest Saturday, August 16, 2014

    BiDatingNow.com is an online bisexual dating site for bisexual singles, bi-curious singles and bi couples to find sexy and open-minded bisexual men, bisexual women and bisexual couples for sexuality and chat . We are online for 13 years and have helped thousands of bisexual singles find their love and romance. If you are looking for sexy singles and couples to explore your sexuality. This is the right place for you. We are the Bisexual Passion and we have more than 300,000 registered members in our network. We have bisexual members from all over the world. Here, you can easily find your bisexual partner in your local area very easily no matter you are from United States, United Kingdom, Australia, Canada or any other country in the world. Do not wait ! Bisexual Dating Site makes it easy to find that special someone to bring your home


  • guest Monday, December 8, 2014

    Hello,

    Does XSL provide date increment function? I have a date field on a template. The value is dynamic. I need to derive a new date based on it by increasing by one day. Could you please advise any solution for that?

    Thanks a lot

    Ray


  • Sravan Tuesday, February 14, 2017

    Hi TEAM,

    HOW TO GET CURRENT MONTH AND CURRENT YEAR IN DASHBOARD PROMPTS OF BI PUBLISHER PARAMETER.

    IT IS VERY URGENT. PLEASE GUIDE ME.. I AM NEW TO BIP REPORTS.


  • Brown Monday, March 6, 2017

    Hi I want to get 'year' from given date.

    For extracting year I wrote the below expression and it is throwing error.

    <?xdoxslt:get_year(xdoxslt:format_date($FROM_DT,’yyyy-mm-dd’,'mm/dd/yyyy',$_XDOLOCALE, $_XDOTIMEZONE),$_XDOLOCALE)?>

    For Extracting monthI wrote the following expression and it was success.

    <?xdoxslt:month_name(xdoxslt:get_month(xdoxslt:format_date($FROM_DT,’yyyy-mm-dd’,'mm/dd/yyyy',$_XDOLOCALE, $_XDOTIMEZONE),$_XDOLOCALE),0,$_XDOLOCALE)?>

    Please help

    Thank You


  • Brown Monday, March 6, 2017

    How to extract year from given date DD/mmm/yyy format?


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