6 Replies Latest reply: Feb 18, 2011 7:47 AM by Miguel Angel Baeyens de Arce RSS

    Problem with date and/or variable and/or WHERE condition

    Nicolas MARTIN

      Good day,

      I spent hours on a problem to finally discover that what I though my script will do wasn't right...

      I have a table that contains all my data, having a date :

       


      TblData:
      LOAD * INLINE [
      MyID, MyDate
      1, 01/01/2010
      2, 31/12/2010
      3, 01/01/2011
      ];


       

      I would like to create a table that contains the lines having the date lower than a specific day.

      Here is the code I used :

       


      LET max_date = makedate(2011, 1, 1);

      TblResult_1:
      LOAD
      MyID AS ResID_1,
      MyDate AS ResDate_1
      RESIDENT TblData
      WHERE num(MyDate) < num($(max_date));


      (I use the "num( )" function to avoid date format problems)

      Unfortunatly, the script returns 0 lines (instead of 2 lines expected) !

       

      After hours of search and tweeks, I found that this code :

       


      LET max_date = num(makedate(2011, 1, 1));

      TblResult_2:
      LOAD
      MyID AS ResID_2,
      MyDate AS ResDate_2
      RESIDENT TblData
      WHERE num(MyDate) < $(max_date);


      returns the 2 lines I expected...

      The only difference is that the "num( )" function is in the variable declaration, instead of the request.

       

      Why is there a difference between the results of my 2 scripts ?