More Date Calculations

Dates, dates, dates ... no not the sweet dried fruit with a pit, we British only ever seem to eat at Christmas, I mean DATES, birsthdates, invoice dates, shipping dates ... they are tough nut to crack in XSL especially calculations. Date calculations are tough right now with publisher .... they will get better but there was a nice workaround provided by Darshan on the forum yesterday, definitely worthy of a wider audience. It was in repsonse to a question on how to calculate the difference between two dates from Valerie.


Valerie was expecting <?xdofx:$C_REPORT_START_DATE-C_DUE_DATE?> to provide a date result, sadly she got an error result. Until we can pick up the xsl2.0 date support in the Oracle XDK engine we are a little stuck on these calculations. Happily Darshan provided two workarounds, one forcing tghe calculation back into the data layer, the other in the layou template.


1) Create one Hidden parameter for that concurrent program and assign default value as ( select :$FLEX$.C_REPORT_START_DATE - :$FLEX$.C_DUE_DATE from dual) and refer this parameter in your layout template. You have to tweak above query to extract correct difference between dates. You could also do the caluculation in the main extraction query.

2) If you still want to do it in template then i would like to suggest you first that convert your dates into number by julius format_code      

  to_number(TO_CHAR(SYSDATE,'JSSSSS'))
and then do substraction/addition. This will give you the difference in seconds.
<?xdofx:tO_NUMBER(TO_CHAR(to_date('2007-08-15', 'YYYY-MM-DD'),'JSSSSS'))
- TO_ NUMBER(TO_CHAR(to_date('2007-08-14', 'YYYY-MM-DD'),'JSSSSS'))?>
Thanks Darshan, its a lot of code in the template but it works very well and as I said we're going to make this better.

Comments:

I am getting a date from the F4211 (TRDJ) which is returned from the sql as a julian date (105138). How can I change this date to "MM/DD/YYYY"so that I can use the date to select data between two dates in BI Publisher?

Posted by Don on August 26, 2008 at 04:45 AM MDT #

Hi don the layout templates do not support a Julian conversion. You would need to do the conversion in the query to get the desired date format. Tim

Posted by Tim on August 27, 2008 at 06:14 AM MDT #

Hi Tim ~ I am working on our payroll check using Check Writer XML RTF. I see that there are many things entries out here for dates, but I am still confused on how to get my date to convert. I have 2009/10/03 and want March 03, 2009. You mention in this posting that this is going to get better. Has anything changed yet that I may be able to take advantage of? I have been playing around with the todate function but have had not luck. Any suggestions? Thanks a bunch. G

Posted by Gwen on October 07, 2009 at 05:50 AM MDT #

Hi, We had customized our Payroll Checks in 11i version. We are now in R12.07. I am suppose to put out Payroll check custimisations into this version. Please help. Where do I start?

Posted by Peri Gevorkian on February 01, 2010 at 07:02 AM MST #

Undoubtedly a skillful write-up mate in this particular topic. I'm wanting to know if I could use this write up on my site, I will link it back aimed at your site though. If this is difficult remember to make me aware and I will take it down right now. Ann

Posted by Loan Diblase on August 03, 2010 at 06:55 AM MDT #

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
« June 2015
SunMonTueWedThuFriSat
 
1
2
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