title: Data value "0" has invalid format error in redshift
body: <p>We are facing a weird problem with one of our query. 
  Below is the query we are running </p>
  
  <pre><code>INSERT into test 
  SELECT 
      member.name as mem_name, 
      CASE WHEN ( member.dob&gt;0 AND length (member.dob)=8 ) THEN (DATEDIFF(year,to_date("dob",'YYYYMMDD'), to_date(20140716,'YYYYMMDD'))) WHEN ( member.dob=0 ) Then 0 END As Age, 
      20140716021501 
  FROM 
      member 
  </code></pre>
  
  <p>Below is the sample data present in our table.</p>
  
  <pre><code>|name           |dob
  |Ajitsh         |0        |
  |rk             |51015    |
  |s_thiagarajan  |19500130 |
  |madhav_7       |19700725 |
  |1922           |0        |
  |rekha          |25478    |
  |vmkurup        |0        |
  |ravikris       |19620109 |
  |ksairaman      |0        |
  |sruthi         |0        |
  |rrbha          |19630825 |
  |sunilsw        |0        |
  |sunilh         |0        |
  |venky_pmv      |19701207 |
  |malagi         |0        |
  |an752001       |0        |
  |edsdf          |19790201 |
  |anuanand       |19730724 |
  |fresh          |19720821 |
  |ampharcopharma |19590127 |
  |Nanze          |19621123 |
  </code></pre>
  
  <p>The date of birth is stored in bigint as <code>YYYYMMDD</code> format.
  In the data there are some rows, in which date is invalid like 0, 51015.
  On some instances this query raises the following error.</p>
  
  <pre><code>INSERT INTO test not successful
  An error occurred when executing the SQL command:
  INSERT into test 
  SELECT 
      member.name as mem_name, 
      CASE WHEN ( member.dob&gt;0 AND length (member.dob)=8 ) THEN (DATEDIFF(y...
  
  ERROR: Data value "0" has invalid format
      Detail: 
      -----------------------------------------------
      error:  Data value "0" has invalid format
      code:      1009
      context:   PG ERROR
      query:     92776
      location:  pg_utils.cpp:2731
      process:   query1_30 [pid=1434]
      -----------------------------------------------
  
  
  Execution time: 3.99s
  
  1 statement failed.
  </code></pre>
  
  <p>But the strange thing is, it raises the error randomly and not all the time.
  Many times it works without any change in query or dataset.
  Sometime it also works in second or third attempt.
  My doubt is that to_date function is giving this error. But why randomly 
  and not gives error on every run.</p>
  
  <p>To support my assumption I also tried this small query.<br>
  <code>SELECT to_date(20140716,'YYYYMMDD'), to_date(0,'YYYYMMDD');</code><br>
  But this also creates the same scenario. It raises error randomly, while
  runs smoothly rest of the times.</p>