title: How to use substring function in oracle in date columns which is in number format
body: <p>I have a Column Called <code>FILLD_DT</code> in a table which has <code>number(8)</code> data type format. I am trying to use this column and create a new column called Derived date in my own table.<br>
  Ex: Filled Date = <code>20170107</code></p>
  
  <p>Below is the query I am using and I am getting </p>
  
  <blockquote>
    <p>ORA-00932: inconsistent datatypes: expected CHAR got NUMBER</p>
  </blockquote>
  
  <pre><code>(CASE WHEN PERD = 'Beginning' AND SUBSTR(FILLD_DT,5,2) IN (01,02,03) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN  '20170101'
        WHEN PERD = 'Beginning' AND SUBSTR(FILLD_DT,5,2) IN (04,05,06) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN '20170501'
        WHEN PERD = 'Beginning' AND SUBSTR(FILLD_DT,5,2) IN (07,08,09) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN '20170701'
        WHEN PERD = 'Beginning' AND SUBSTR(FILLD_DT,5,2) IN (10,11,12) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN  '20171001'
   ELSE FILLD_DT
   END ) AS DRVD_DATE
  </code></pre>
  
  <p>please help me understand</p>
  
  <p>below is the full query</p>
  
  <pre><code>SELECT DISTINCT
  SRC_ID,
  SEQ_NBR,
  PROD_ID,
  TYPE_CD,
  FILLD_DT,
  PRCSD_DT,
  (CASE WHEN PERD = 'Beginning' AND SUBSTR(FILLD_DT,5,2) IN (01,02,03) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN  '20170101'
   WHEN PERD = 'Beginning' AND SUBSTR(FILLD_DT,5,2) IN (04,05,06) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN '20170401'
  WHEN PERD = 'Beginning' AND SUBSTR(FILLD_DT,5,2) IN (07,08,09) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN '20170701'
  WHEN PERD = 'Beginning' AND SUBSTR(FILLD_DT,5,2) IN (10,11,12) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN  '20171001'
  WHEN PERD = 'middle' AND SUBSTR(FILLD_DT,5,2) IN (01,02,03) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN  '20170115'
   WHEN PERD = 'middle' AND SUBSTR(FILLD_DT,5,2) IN (04,05,06) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN '20170415'
  WHEN PERD = 'middle' AND SUBSTR(FILLD_DT,5,2) IN (07,08,09) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN '20170715'
  WHEN PERD = 'middle' AND SUBSTR(FILLD_DT,5,2) IN (10,11,12) AND SUBSTR(FILLD_DT,1,4) = 2017  THEN  '20171015'
  ELSE FILLD_DT
  END)
  FROM
  SOURCE
  </code></pre>
  
  <p>and I modified the query to your suggestion and I am getting the below error</p>
  
  <blockquote>
    <p>[Error] Execution (8: 29): ORA-01830: date format picture ends
    before converting entire input string</p>
  </blockquote>
  
  <pre><code>SELECT DISTINCT
  SRC_ID,
  SEQ_NBR,
  PROD_ID,
  TYPE_CD,
  FILLD_DT,
  PRCSD_DT,
  ( trunc( to_date( FILLD_DT || '01', 'yyyymmdd' ), 'Q' )
   ) AS DRVD_DATE
  FROM
  SOURCE
  WHERE
  SRC_ID = 163662
  </code></pre>