4 Replies Latest reply: Apr 4, 2012 9:18 AM by Magnus Johannesson RSS

    Compare dates in Load statement

      I'm trying to add a flag field in a Load statement by comparing a date field in the database to a date in a variable. The variable is set like this:

      LET vCompareDate = date(AddMonths(Peek('UptimeDate', -1, 'UptimePerDayTemp'), -1), 'DD/MM/YY');

       

      And the Load statement looks like this:

      LOAD

      UptimeDate,

      If (UptimeDate > $(vCompareDate), 1, 0) as LastMonthFlag

      Resident UptimePerDayTemp;

       

      But I get only 1, i.e. the If statement always evaluates to True. Why?

        • Compare dates in Load statement
          Dennis Hoogenboom

          Most of the time this is a format issue.

           

          What you should do is compair the format of your field: UptimeDate with the format of your variable $(vCompareDate)

           

          In other words does the data in the field "UptimeDate" the same as when you show your  variable $(vCompareDate) (fe) in a inputbox.

            • Compare dates in Load statement

              I added an input box to show the value of the vCompareDate variable and it turned out that the formats differed, so I changed the format of the variable like this:

              LET vCompareDate = date(AddMonths(Peek('UptimeDate', -1, 'UptimePerDayTemp3'), -1), 'M/D/YYYY');

               

              But the If statement still only evaluates to True.

               

              The UptimeDate field in the table contains every date from 1/4/2010 to 4/1/2012.

               

              The vCompareDate variable contains "3/1/2012" (1st of March).

                • Compare dates in Load statement
                  Dennis Hoogenboom

                  Does your UptimeDate field als has the format 'M/D/YYYY' ?

                  (just making sure it is not 'D/M/YYYY' or maybe 'MM/DD/YYYY')

                   

                  If so try to Floor your date :

                  Date(FLOOR(UptimeDate) , 'M/D/YYYY')

                   

                  I am pretty sure it's a format thing...

                    • Compare dates in Load statement

                      Yes, they both have the format 'M/D/YYYY'. I tried your suggestion without any luck, but then I tried using Num instead (since you thought it was a format thing), and now it works.

                       

                      The new variable assignment:

                      LET vCompareDate = Num(AddMonths(Peek('UptimeDate', -1, 'UptimePerDayTemp'), -1));

                       

                      The new Load statement:

                      LOAD

                      UptimeDate,

                      If (Num(UptimeDate) > $(vCompareDate), 1, 0) as LastMonthFlag

                      Resident UptimePerDayTemp;

                       

                      Thanks for your help!