Groovy Date Functions

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:

date_diff

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.

Parameters:
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
Returns:
the difference in dates in the desired format

For example

<?xdoxslt:date_diff('d','2009-09-14', '2009-09-20','en-US',1)?>

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.

<?for-each:xdoxslt:foreach_number($_XDOCTX,2,10,1)?>
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

<?xdoxslt:ora_format_date_offset('2009-06-03',position(), '+')?>

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.
Groovy Baby!

Comments:

Is it ok to bring flowers on a first date?

Posted by Ann Leahy on January 12, 2010 at 01:10 PM MST #

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
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
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