Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Wednesday, October 24, 2018

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

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect