{"id":140,"date":"2010-08-12T01:05:41","date_gmt":"2010-08-12T05:05:41","guid":{"rendered":"http:\/\/lschofield.net\/technical\/?p=140"},"modified":"2010-08-12T01:05:41","modified_gmt":"2010-08-12T05:05:41","slug":"date-subtraction-in-ireports","status":"publish","type":"post","link":"http:\/\/www.lschofield.net\/technical\/date-subtraction-in-ireports\/","title":{"rendered":"Date Subtraction in iReports"},"content":{"rendered":"<p>I&#8217;m using iReports a lot for general business reporting, which is an open source reporting tool in the spirit of Crystal Reports.\u00a0 It merges SQL and Java, creating report &#8220;documents&#8221; which are an XML file which tells how to attach to the database, run the query, and then format the results into a pretty document with fonts and things.<\/p>\n<p>A lot of times you&#8217;re working with dates and trying to understand the age of things.\u00a0 iReports lets you create variables and attach Java code to them, which gives infinite flexibility for logic and conversion.\u00a0 Unless, of course, you don&#8217;t know Java in which case its a bewildering concoction of spaces, periods, parens, and references to mysterious methods and objects in classes buried in attached, unseen, places.\u00a0 A visit to a number of forums focused on Java or iReports will connect you to Java experts who berate you to read API documentation.<\/p>\n<p>Or, use the DATEFIFF function in SQL (MySQL, in this case) to do the math and assign it to a SQL field:<\/p>\n<pre>SELECT\n DATEDIFF(table.`date_pickedup`, table.`date_arrival`) AS agedInSystem,\n DATEDIFF(table.`date_pickedup`, table.`date_service`) AS agedSinceDOS<\/pre>\n<pre>FROM table<\/pre>\n<p>No point spending time in Java when SQL has a canned function for this.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m using iReports a lot for general business reporting, which is an open source reporting tool in the spirit of Crystal Reports.\u00a0 It merges SQL and Java, creating report &#8220;documents&#8221; which are an XML file which tells how to attach to the database, run the query, and then format the results into a pretty document [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":""},"categories":[6],"tags":[53,55],"_links":{"self":[{"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/posts\/140"}],"collection":[{"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/comments?post=140"}],"version-history":[{"count":0,"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/posts\/140\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/media?parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/categories?post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.lschofield.net\/technical\/wp-json\/wp\/v2\/tags?post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}