title: How to calculate days between two date columns in db2?? how do i correct the difference of days in this query?
body: <p>my query :</p>
  
  <pre><code>SELECT CD1
  , OD1
  ,CHAR(DATE(SUBSTR(CHAR(CD1),1,4) ||'-'||
      SUBSTR(CHAR(CD1),5,2) ||'-'||
     SUBSTR(CHAR(CD1),7,2)), USA)
  AS "CDate_Conversion"
  ,CHAR(DATE(SUBSTR(CHAR(OD1),1,4) ||'-'||
      SUBSTR(CHAR(OD1),5,2) ||'-'||
     SUBSTR(CHAR(OD1),7,2)), USA)
  AS "OConv"
  
  , CD1-OD1 AS Days
  
  FROM PDAT.ZPKD
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>Crt Date Date Ordered CDate_Conversion OConv    DAYS
  20171201    20171130    12/01/2017  11/30/2017  71
  20171003    20170929    10/03/2017  09/29/2017  74
  20171009    20170908    10/09/2017  09/08/2017  101
  20171009    20170921    10/09/2017  09/21/2017  88
  20171002    20170929    10/02/2017  09/29/2017  73
  20171009    20171006    10/09/2017  10/06/2017  3
  20181010    20181010    10/10/2018  10/10/2018  0
  20180723    20180723    07/23/2018  07/23/2018  0
  20180710    20180709    07/10/2018  07/09/2018  1
  20181010    20181009    10/10/2018  10/09/2018  1
  20180831    20180830    08/31/2018  08/30/2018  1
  20180827    20180814    08/27/2018  08/14/2018  13
  20180828    20180827    08/28/2018  08/27/2018  1
  20180403    20180403    04/03/2018  04/03/2018  0
  20180405    20180403    04/05/2018  04/03/2018  2
  20180820    20180820    08/20/2018  08/20/2018  0
  20180920    20180919    09/20/2018  09/19/2018  1
  20180305    20180305    03/05/2018  03/05/2018  0
  20180306    20180305    03/06/2018  03/05/2018  1
  </code></pre>
  
  <p>The difference of days is incorrect in first 5 rows.</p>
  
  <p>also,i am not sure how to find difference of days with the converted columns so instead i chose CTD-OTD !</p>
  
  <p>and there is no time column for OTD, so cant use TIMESTAMPDIFF .</p>
  
  <p>note : i randomly selected the rows with wrong difference of days to show the resuly=ts here.</p>