Date Functions in Oracle XMLP Reports

SQL/XSL	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. 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-P','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-8T21: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