calculate number of days from date fields

[elikalily]

HI,

I am trying to add an extra column in an existing report.

The extra column i want to create would calculate the diference in days between 2 date fields.

One date field (current date) has the text field expression = new java.util.Date(), class java.util.Date

The other data field (Ex-factory date) has the text expression field = $F{Ex-Factory Date}, class java.lang.String

Does anyone know how can I create a field that would generate results for  'Ex-Factory Date - current date =  n days' ?

Any ideas?

 

 

Post Edited by elikalily at 08/16/2011 07:50

Post Edited by elikalily at 08/16/2011 23:33

Post Edited by elikalily at 08/16/2011 23:34

[cbarlow3]

I think the answer depends on three things:

1. Whether it makes more sense for you to do the math in your original query (SQL, probaly) or in the scripting language you're using in JasperSoft (probably JavaScript or Groovy),

2. If you're using SQL and decide to create the new field directly in your SQL query, which version of SQL are you using (the syntax for various date arithmetic varies from one flavor to another), and

3. What format is your $F{Ex-Factory Date} field in and why on earth is it a text field instead of a true date field?

For the sake of argument, I'll assume that you want to create an actual column in your SQL result set rather than just create a text field in the Design view that is based on some scripting work, and I'll assume that the format of the String is MM/DD/YYYY.  The first thing you'd have to do is convert that string into an actual date field.  So instead of

SELECT
  mt.ExFactoryDate as ExFactoryDate,   // this is a String
  mt.currentDate as currentDate              // this is a Date
FROM
  myTable as mt

your first improvement might be this:

SELECT
  DATE(SUBSTR(mt.ExFactoryDate,7,4) || '-' ||
       SUBSTR(mt.ExFactoryDate,1,2) || '-' ||
       SUBSTR(mt.ExFactoryDate,4,2)) as ExFactoryDate,  // this is a Date
  mt.currentDate as currentDate                                       // and so is this
FROM
  myTable as mt

This assumes that you don't have null values in mt.ExFactoryDate and that these strings are VERY consistently formatted!  Your next step would be to add another column that is one date subtracted from another.  Rather than repeat all the logic to come up with that date value again, we'll turn the previous query into an inner query and pull the ExFactoryDate value by name.  The exact syntax of the date subtraction varies with the SQL flavor.  In DB2 it's days(laterDate) - days(earlierDate).  In MySQL and SQLServer you use the datediff function:  datediff function: MySQL: datediff(earlierDate, laterDate)  SQL Server:  datediff(Day, laterDate, earlierDate)   (where "Day" is an actual literal--it specifies what kind of a date difference you want...there are lots of options).  In DB2, the final query looks like this:

SELECT
 t.ExFactoryDate,
 t.currentDate,
 days(t.currentDate) - days(t.ExFactoryDate) as daysBetween
FROM
(
SELECT
  DATE(SUBSTR(mt.ExFactoryDate,7,4) || '-' ||
       SUBSTR(mt.ExFactoryDate,1,2) || '-' ||
       SUBSTR(mt.ExFactoryDate,4,2)) as ExFactoryDate,
  mt.currentDate as currentDate
FROM
  myTable as mt
) AS t

Hope that helps!

Carl

P.S.: I gave date subtraction syntax for DB2, SQL Server, and MySQL.  I forgot to mention that for Oracle and PostgreSQL, you can subtract two dates directly:

t.currentDate - t.ExFactoryDate as daysBetween

[elikalily]

Thanks for the reply cbarlow3.

After a few days spent on this, teh report is now working and the easiest way to do that was editing the sql query, adding datediff() function.

datediff(day,ExFactoryTo, getdate() ) "DateDiff"

Then on ireport I created a text field to display DateDiff field.

[lbruen]

 Hi,

Thank you, I have found these comments very helpful. Although would it be possible to find the difference between two dates yet excluding weekends (UK bank holidays at a push)?

Thanks

 

[cbarlow3]

Yes, that's possible. How you do it depends on what flavor of SQL you're using.  I think the first trick is to just GENERATE all dates from a starting date to an ending date (which of course could be useful for many things anyway), and then figuring out which ones you want to filter out based on day of week, holidays, etc.  Then if you just want to COUNT what you get back, that's easy enough.  It's going to be a little time-consuing, so if your flavor of SQL supports common table entries (CTE--WITH statements, essentially) and you are going to be doing a lot of these calculations in a single query, then you should probably first build a temporary table of all workdates in the full range you need (1/1/2000 to Today + 30 days or whatever the case might be).  Then for each calculation, you would just select all workdates from that temporary table that are between a given startdate and enddate, where you've made sure that each startdate and enddate you want to do the calculation for fall within the original startdate and enddate range you used to generate the temporary table.

Based on your question, I wrote a version of  this this morning for DB2 SQL.  So far, it will take a startdate and enddate and generate all dates in that range.  Then it selects all those dates that are not Saturday, are not Sunday, are not January 1, are not a Friday Dec 31 (which is when you would presumably have New Year's Day off if Jan 1 fell on a Saturday), are not Monday Jan 2 (again, an observed date).  This weekend I'll add an example of a holiday that follows the "Third Monday in May" type rule so you can see how that's done as well. 

Sorry to tantalize you with only the DESCRIPTION of a solution, but I won't have time to wrap it up until tomorrow.

Carl

[cbarlow3]

Okay, I'm attaching my jrxml code for a report where you input startdate and enddate, and it lists every date in that range, plus what day of the week it is and a "Category", where "Category" is either "Saturday", "Sunday", the name of a holiday (or in some cases I put the holiday name and "(observed)" when the normal holiday date falls on a Saturday or Sunday), or...if the day isn't any of those categories, I put "Working Day".  Obviously, instead of printing all these dates, it would be very easy to just select the ones that are "Working Day" and count them.

A few things to watch out for:  my version not only uses the CTE (WITH) method of first building a temporary, named table that can be reused, it also uses recursion.  If your version of SQL doesn't support recursion, you'll probably need to instead define a table of VALUES from 1to whatever your maximum number of days is between startdate and enddate that you'll support.

Other things to watch out for:  my current code uses U.S. Federal Holidays, not U.K., although I've shown the two most common situations you need to deal with:  a fixed date that requires special handling when it falls on a Saturday or Sunday, and a holiday that is defined as the xth y-day of month-Z.  I don't have Easter or any other religious holidays that are based on full moons, etc. ...it can be done, but it's a significant jump in complexity.

I also output my dates in mm/dd/yyyy format...we yanks are pretty illogical that way.

Good luck!

Carl

Post Edited by cbarlow3 at 01/29/2012 05:20

[lbruen]

That is brilliant thank you so much Carl! No problem about the US format :) I'm out until the end of next week, will have a closer play around then.

Thanks again!

[cbarlow3]

Actually, calendrical calculations are something of a hobby for me, so I decided to also add the code for calculating Easter (by the current Western definition, which is "First Sunday AFTER the Paschal Moon, which is the first (calculated) full moon on or after March 21".  Because I made my CASE statement treat Saturday and Sunday as their own category, more important than any holidays, I decided that although I calculate Easter (on which several other religiouis holidays are based), I would use "Good Friday" (Friday before Easter) as my example holiday to display.  Easter still shows up as category "Sunday".  Of course, if in the U.K. you normally get the Monday after Easter off, it would be a simple change.  Or if you decided that you wanted Easter to be more important than category "Sunday", you would just include a CASE clause for Easter earlier in the CASE statement than the "Sunday" test.  Notice, I only calculate the Easters for the years in the date range one time and then use that small number of dates in the main query rather than trying to calculate Easter each time I want to categorize a date.

I think I'll also post this under a different subject in case it's helpful to others as well.

Carl