Oracle BI Publisher Blog

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.