title: Date arithmetic in SQL on DB2/ODBC
body: <p>I'm building a query against a DB2 database, connecting through the IBM Client Access ODBC driver. I want to pull fields that are less than 6 days old, based on the field 'a.ofbkddt'... the problem is that this field is not a date field, but rather a DECIMAL field, formatted as YYYYMMDD.</p>
  
  <p>I was able to break down the decimal field by wrapping it in a call to char(), then using substr() to pull the year, month and day fields. I then formatted this as a date, and called the days() function, which gives a number that I can perform arithmetic on.</p>
  
  <p>Here's an example of the query:</p>
  
  <pre><code>select
         days( current date) -
         days( substr(char(a.ofbkddt),1,4) concat '-'              -- YYYY-
               concat substr(char(a.ofbkddt),5,2) concat '-'       -- MM-
               concat substr(char(a.ofbkddt),7,2) ) as difference, -- DD
         a.ofbkddt as mydate
  from QS36F.ASDF a
  </code></pre>
  
  <p>This yields the following:</p>
  
  <pre><code>difference  mydate
  2402        20050402
  2025        20060306
  ...
  4           20110917
  3           20110918
  2           20110919
  1           20110920
  </code></pre>
  
  <p>This is what I expect to see... however when I use the same logic in the where clause of my query:</p>
  
  <pre><code>select
         days( current date) -
         days( substr(char(a.ofbkddt),1,4) concat '-'              -- YYYY-
               concat substr(char(a.ofbkddt),5,2) concat '-'       -- MM-
               concat substr(char(a.ofbkddt),7,2) ) as difference, -- DD
         a.ofbkddt as mydate
  from QS36F.ASDF a
  where
  (
  days( current date) -
  days( substr(char(a.ofbkddt),1,4) concat '-'         -- YYYY-
        concat substr(char(a.ofbkddt),5,2) concat '-'  -- MM
        concat substr(char(a.ofbkddt),7,2) )           -- DD
  ) &lt; 6
  </code></pre>
  
  <p>I don't get any results back from my query, even though it's clear that I <em>am</em> getting date differences of as little as 1 day (obviously less than the 6 days that I'm requesting in the where clause).</p>
  
  <p>My first thought was that the return type of days() might not be an integer, causing the comparison to fail... according to the documentation for days() found at <a href="http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmst02.htm" rel="nofollow">http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmst02.htm</a>, it returns a bigint. I cast the difference to integer, just to be safe, but this had no effect.</p>