title: OBIEE: Casting String to date then date to string
body: <pre><code>FILTER("source"."recordCount" USING "source"."snapshot_date" = 
      EVALUATE('TO_CHAR(%1, ''YYYYMMDD'')', TIMESTAMPADD(SQL_TSI_DAY, -7,  EVALUATE('TO_DATE(%1, %2)', "source"."snapshot_date" , 'YYYYMMDD'))))
  </code></pre>
  
  <p>So i have this piece of code here. I know some will say "Just use the AGO function" But somehow it's causing problems because of it's connection with other tables so what I'm trying to achieve here is like a remake. The process goes this way:</p>
  
  <p>The snapshot_date there is actually in varchar format and not date. So it's like "20131016" and I'm trying to change it to a date then subtract 7 days from it using the TIMESTAMPADD function and then finally returning it back to varchar to use it with FILTER.</p>
  
  <p>This snippet somehow works when testing the FILTER using hardcoded values like "20131016" for example but when tested out with the code above all the row are blank. On paper, the process i assumed would happen goes lke this. "20131016" turns to a date with a format of 20131016 (yyyymmdd) and then less 7 days: 20131009 and then turned into char again "20131009" to be used in the filter.</p>
  
  <p>But somehow that doesn't happen. I think the data format is not applying either to the string->date or the date->string conversion. which results to the values not getting a match at all.</p>
  
  <p>Anyone have any idea what's wrong with my code? </p>
  
  <p>By the way I've already tried to use CAST instead of EVALUATE or TO_TIMEDATE with the same result. Oh and this goes to the formula of the column in BMM.</p>
  
  <p>Thanks</p>