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 Expression Usage Description
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. The fmt is a date format specifying the format of char. 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_name This 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.
Comments:

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

Posted by Vetri on September 08, 2008 at 04:23 AM MDT #

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

Posted by John on March 17, 2009 at 11:53 AM MDT #

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

Posted by kumar on January 25, 2010 at 06:24 PM MST #

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

Posted by guest on February 22, 2012 at 08:54 PM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

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!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today