title: DB2 Convert Number to Date
body: <p>For some reason (I have no control over this) dates are stored as Integers in an iSeries AS400 DB2 system that I need to query.  E.g. today will be stored as:</p>
  
  <pre><code>20,171,221
  </code></pre>
  
  <p>Being in the UK I need it to be like the below in Date format:</p>
  
  <pre><code>21/12/2017
  </code></pre>
  
  <p>This is from my query:  (OAORDT = date field)</p>
  
  <pre><code>Select
  Date(SUBSTR( CHAR( OAORDT ),7,2) ||'/' || SUBSTR(CHAR ( OAORDT ),5,2) || '/' || SUBSTR(CHAR (OAORDT ),1,4)) AS "Order Date"
  from some.table
  </code></pre>
  
  <p>However, all I get is Nulls.  If I remove the Date function, then it does work but its now a string, which I don't want:</p>
  
  <pre><code>Select
  SUBSTR( CHAR( OAORDT ),7,2) ||'/' || SUBSTR(CHAR ( OAORDT ),5,2) || '/' || SUBSTR(CHAR (OAORDT ),1,4) AS "Order Date"
  from some.table
  </code></pre>
  
  <p>How do I convert the OAORDT field to Date?</p>
  
  <p>Just to update - I will be querying this from MS SQL Server using an OpenQuery</p>
  
  <p>Thanks.</p>