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'))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.
- TO_ NUMBER(TO_CHAR(to_date('2007-08-14', 'YYYY-MM-DD'),'JSSSSS'))?>
Comments (3)
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 | August 26, 2008 10:45 AM
Posted on August 26, 2008 10:45
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 | August 27, 2008 12:14 PM
Posted on August 27, 2008 12:14
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 | October 7, 2009 11:50 AM
Posted on October 7, 2009 11:50