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

      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 :


      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);

      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));

      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 ?