4 Replies Latest reply: Oct 3, 2013 11:06 AM by Dmitry Gudkov RSS

    String Date YYYYMMDD > Epoch date comparison

    Robert Fishel

      Hello my QV friends.  I have been stuck on a problem for a couple of days now and just cannot figure out how to resolve the issue.  I am going to try to be as specific as possible. 

       

      I have created a variable named vTwoWeeksAgo.  It calculates today's date minus 14 days.

       

      SET vTwoWeeksAgo='';
      LET vTwoWeeksAgo=Date(Today()-14);
      

       

      I then use a LOAD statement that has the following field:

       

      LOAD 
      DATE(DATE#(PDDCR, 'YYYYMMDD'), 'MM/DD/YYYY') AS DATECREATED,
      FROM TABLE_A;
      

       

      Then in another LOAD statement I am trying to do the comparison:

       

      LOAD *
      RESIDENT TABLE_A
      WHERE DATECREATED > $(vTwoWeeksAgo);
      

       

      I "think" the problem is that I have two different types of dates.  My DateCreated field has the dates formatted first as YYYYMMDD, which I have converted to MM/DD/YYYY.  I then want to be able to find records that have this date greater than my variable.  However, I think my variable is being represented as the epoch date, a five digit date.  Please, what am I doing wrong?  Thanks so much