Groovy Date Functions
By Tim Dexter-Oracle on Sep 16, 2009
Multiple questions lately on working with dates, how can I work out the number of days between two dates? How can I create a list of consecutive dates, the list goes on. I have mentioned the lack of native date support in BIP ... it still uses a hybrid of XSL1.0 and 2.0, that's a whole other story.
To plug some of those gaps there are a bunch of data functions that are available now, couple of the more interesting ones can help to answer the two questions above.
There is an extension function you can use, from the javadoc and as Leslie has reminded me, the user docs:
public static long date_diff( format, fromDate, toDate, locStr, tzID)
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 Gregorian calendar.
format - the format to which the difference is required; allowed formats are y (for Year), m(for month), w(for week), d(for day), h(for hour), mi(for minute), s(for seconds) and ms(for milliseconds)
fromDate - the first date
toDate - the second date
locStr - locale string -> lang-Territory
tzID - timezone ID ->http://java.sun.com/j2se/1.4.2/docs/api/java/util/TimeZone.html
the difference in dates in the desired format
gives a result of 6!
You can substitute in columns for the dates, just remember the date format required.
The other question deals with generating a list of consecutive dates. Could solve it with a nice recursive template function, bleeeuuch! BIP has a nice 'for...next' loop function. Gotta thank 'Vetsrini' for jumping on this in the forum.
Hello - <?position()?> = <?xdoxslt:sysdate() + position()?> = <?xdoxslt:ora_format_date_offset('2009-06-03',position(), '+')?>
The first line is the for...next loop, specifically the xdoxslt:foreach_number($_XDOCTX,2,10,1) command. By the numbers, 2 is our starting point, 10 is the stopping point and 1 is the step. Any of these can be substituted with column data values.
We have the loop to generate the list of numbers, now we want to generate consecutive dates by adding each number to a date. The following command
does the business. We are starting with a fixed date (XSD format) but you could equally use a column value.
Running the command gives us:
Hello - 1 = 90917 = 2009-06-02T00:00:00.000+00:00
Hello - 2 = 90918 = 2009-06-03T00:00:00.000+00:00
Hello - 3 = 90919 = 2009-06-04T00:00:00.000+00:00
Hello - 4 = 90920 = 2009-06-05T00:00:00.000+00:00
Hello - 5 = 90921 = 2009-06-06T00:00:00.000+00:00
Hello - 6 = 90922 = 2009-06-07T00:00:00.000+00:00
Hello - 7 = 90923 = 2009-06-08T00:00:00.000+00:00
Hello - 8 = 90924 = 2009-06-09T00:00:00.000+00:00
Hello - 9 = 90925 = 2009-06-10T00:00:00.000+00:00
Cool huh? Yep, you get long XSD date formats, but you can format those quite easily.