title: normalize date in postgres - character to date
body: <p>I have a Table with coloumnname <code>description</code></p>
  
  <pre><code>description  //character varying
  LZ000834_28-02-14
  LZ000834_28-02-14
  LA20683_30-04-15
  LA20683_30-04-15
  LA20300_31-01-15
  LA20300_31-01-2015
  LA20264_31-01-15
  LA20264_31-01-2016
  LAN2078_31-03-16
  LAN2078_31-03-15
  LAN8394_31-04-14
  L2Z82736_31_03_2015  //has 1million rows
  </code></pre>
  
  <p>here description means batchname_expirydate</p>
  
  <p>my question is how do I normalize my description column convert all those dates to <code>DD-MM-YY</code> format</p>
  
  <p>i have tried this two queries</p>
  
  <pre><code>select substring(description from position('_' in description) +1) from attributesetinstance;
  </code></pre>
  
  <p>above query will give me all the strings then i tried date conversion like this</p>
  
  <pre><code>select to_date(substring(description from position('_' in description) +1), 'DD-MM-YY') from attributesetinstance;
  </code></pre>
  
  <p>now this gives me error </p>
  
  <pre><code>ERROR:  invalid value "_3" for "DD"
  DETAIL:  Value must be an integer.
  
  
  ********** Error **********
  
  ERROR: invalid value "_3" for "DD"
  SQL state: 22007
  Detail: Value must be an integer.
  </code></pre>
  
  <p><strong>how do update/recorrect all my database ?</strong></p>
  
  <p><strong>update:</strong></p>
  
  <p>tried with another sql</p>
  
  <pre><code>with product_dates AS (
  select description, ((val[2])||'-'||val[3]||'-'||val[4])::date as expir_date
  from ( 
      select description,regexp_matches(description, '([a-zA-Z0-9]+)_([0-9]+)[-_]([0-9]+)[-_]([0-9]+)') as val 
      from attributesetinstance
  ) a
  ), expiring_dates AS (
  select description from product_dates
  )
  select description from expiring_dates
  </code></pre>
  
  <p>i get following error:</p>
  
  <pre><code>ERROR:  date/time field value out of range: "31-04-14"
  
  
  ********** Error **********
  
  ERROR: date/time field value out of range: "31-04-14"
  SQL state: 22008
  </code></pre>
  
  <p><strong>update</strong></p>
  
  <p>my postgres datestyle</p>
  
  <pre><code>show datestyle;
  "ISO, DMY"
  </code></pre>