4 Replies Latest reply: Aug 3, 2011 4:59 PM by ggoyal_123 RSS

    date = today() problem

      Hi All,

       

      What is wrong with the below statement? It doesnt seem to return anything.

       

      =IF(date(END_TIME) = TODAY(), END_TIME)

       

      Thanks!

        • date = today() problem
          John Witherspoon

          I'm guessing END_TIME is a timestamp?  The date() function is a formatting function only, so you're still comparing a timestamp to a date.  Try using the floor() or daystart() function instead to remove the time portion and leave you with a date.

            • date = today() problem

              Hi John,

               

              With date(end_time), it does extract the date for me.. just that it somehow doesnt work then i try and use the = sign.

              Floor and daystart are not working either.

               

              I am attaching the application to show you the data.

                • Re: date = today() problem
                  John Witherspoon

                  Floor() and daystart() both worked just fine for me in your file.  Here are the working expressions in case there was some confusion as to what I was suggesting:

                   

                  =IF(floor(END_TIME) = TODAY(), END_TIME)
                  =IF(daystart(END_TIME) = TODAY(), END_TIME)

                   

                  As far as date() goes, date() is a formatting function.  It is FORMATTING the field as a date.  That does not change how it is stored internally.  When you use =, you are comparing the INTERNAL values, not the displayed values.  A timestamp formatted as a date is still a timestamp, so won't match a date unless it happens to be exactly midnight.

                   

                  Set analysis behaves differently, as it is using the search expression logic internally, which results in a text comparison, so a date() function there WOULD work.  But it won't work in an if() - one of those little internal inconsistencies that annoy me, but I live with.