10 Replies Latest reply on Jul 25, 2019 4:24 AM by Sinisa Mikor

    Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?

    Devin Lindsay
      Share This:

      I'm running into an issue where time on a column is causing issues:

       

      If I create a Calculated Field and use the Day Function on a Start Date column and have a result in Start Date like 07/27/2017 12:00 AM then the Day shown in the new calculated field is '26' when it should be showing '27' which I assume has something to do with the 12:00 AM time.

       

      Likewise, if I do a Calculated Field with the Date Difference Function and have it go between Start Date and Target Date I can run into issues.  If Start Date is 07/27/2017 2:35 PM and the Target Date is 07/27/2017 2:30 PM, I will get a result of -1 (day).  What I am attempting to do is find all tickets completed by a target date.  If the target date is 07/27/2017, I don't care when it was done on 07/27/2017, just that it was completed on that day.

       

      Any help is appreciated. Thank you.

        • 1. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
          LJ LongWing

          you might want to create a date only field and do a setfield from the date/time into the date only, then do your calculations on that field, it removes the 'time' variable.

          • 2. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
            Shreyas Swamy

            Can you share some screenshots and SQL being generated? The usage of Day function shouldn't change the day value based on timestamp.

            Also check the timezone setting in your Smart Reporting instance against the application timezone being shown (your windows/browser timezone)

            • 4. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
              Sinisa Mikor

              For starters, you shouldn't use DAY function because it returns day of month so you're bound to see errors when starting and ending date aren't from the same month and year, and the day part of latter is smaller than the day part of former.  Instead, you could either use DATE function to get value of day, month and year before comparing the two, or simply use Date Difference - In Days, ARJDBC.

               

              For example, to check if work order was completed by target date, you could define calculated field using Date Difference - In Days, ARJDBC with Completed Date as Start Date and Estimated Resolution Date as End Date or, if you prefer freehand SQL, DATEDIFF('day', `WOI:WorkOrder`.`Completed Date`, `WOI:WorkOrder`.`Estimated Resolution Date`).  Value of zero would mean that work order was completed on targeted date, positive values would indicate how many days before target date was it completed, and negative values would mean how many days after target date was work order completed.  (Initially, column might be formatted as numeric and show two decimal places -- there is no need to change format, but there is no need for decimal places either so set that value to zero.)

               

              If you don't want to display actual difference, use calculated field as filter -- as stated above, positive values will show work orders completed on target date or earlier, negative values will show work orders completed after target date.

              • 5. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
                Devin Lindsay

                Right, I'm not using the day function. I meant to type Date function like is in the post title and in the second paragraph of my initial post.

                 


                I am using the Date Difference - In Days, ARJDBC with the correct start/complete dates.  However, since the date has a timestamp, if I calculate the Date Difference and the complete and start dates are the same, it will give me different numbers based on what is in the time.  As my second paragraph in the previous post stated, If Start Date is 07/27/2017 2:35 PM and the Target Date is 07/27/2017 2:30 PM, I will get a result of -1 (day). This would be the same value (-1) as a Start Date on 07/28/2017 at 2:15 PM. However, my report should show the incident completed on the same day as being on time whereas the incident completed the day after as being late. Since both are -1, I will have incorrect data showing them both as being late.

                 

                 

                 

                When I initially ran into this issue, I figured it would be an easy fix by making a new calculated field using the Date, ARJDBC function where I would be able to change a value like 07/27/2017 2:35 PM into 0/7/27/2017.  I thought, GREAT! There's no timestamp, and I gave it the name of Start Date - No Timestamp. I did the same for my Target date. Then I did a simple SQL calculation on those two new fields as follows: " CASE WHEN Change Target Date IS NULL THEN 'No Target Date' WHEN Start Date no Timestamp <= Target Date no Timestamp THEN 'Before' WHEN Start Date no Timestamp > Target Date no Timestamp THEN 'After' END"

                 

                Sadly, this was still considering the time on the original Start Date column and placed some incidents into "After" when they should be labeled as "Before". So if originally they had the same date but the Start time was after the Target time (even though I removed the time portion in this calculated field) it would show up as "After"

                 

                I hope that clarifies things a bit.

                • 6. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
                  Sinisa Mikor

                  Since time part crops up in the background even when using date only value of datetime attributes, you could make sure that it is discarded by replacing datetime with datetime - (datetime % number_of_seconds_in_a_day); these will not look good if displayed, but values will be the same regardless of time of day so you'll be able to compare two datetime values the way you need to.  For example:

                   

                  • calculate Completed day as  `WOI:WorkOrder`.`Completed Date` - (`WOI:WorkOrder`.`Completed Date` % (3600*24))
                  • calculate Targeted day as `WOI:WorkOrder`.`Estimated Resolution Date` - (`WOI:WorkOrder`.`Estimated Resolution Date` % (3600 * 24))
                  • calculate Days until target as (Completed day - Targeted day) / 86400 to calculate difference in days  -- you'll have to use freehand SQL to enter something like ((`WOI:WorkOrder`.`Estimated Resolution Date` - (`WOI:WorkOrder`.`Estimated Resolution Date` % (3600 * 24))) - (`WOI:WorkOrder`.`Completed Date` - (`WOI:WorkOrder`.`Completed Date` % (3600*24)))) / (3600 * 24)

                   

                  Positive values of Days until target would indicate timeliness, negative values would indicate tardiness.

                   

                  2 of 2 people found this helpful
                  • 7. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
                    Devin Lindsay

                    I get no results when I do like you mentioned:

                    'StartDate' - ('StartDate' / (3600*24))

                     

                    When you say: "calculate Completed day as  `WOI:WorkOrder`.`Completed Date` - (`WOI:WorkOrder`.`Completed Date` % (3600*24))"

                    can this not be done with the "Simple' Formula type? Or does this have to be done in Freehand?

                    Also, if I were to try this in Freehand, what would I use as the table.columnname?  I have a folder on the left called "Date and time" and in it is the column "Scheduled Start Date" but when I try to use that in Freehand, I'm getting errors. Should it be entered like this?: 'Date and time'.'Scheduled Start Date' or in some other way? 'Scheduled Start Date' was said to be ambiguous (no table name).

                     

                     

                    I must be missing something here.

                    • 8. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
                      Sinisa Mikor

                      Be careful, 'StartDate' - ('StartDate' / (3600*24)) almost always give very different results than 'StartDate' - ('StartDate' % (3600*24)): / is simple division operater which returns exact quotient of a / b, while % returns modulo (remainder) of division of a by b.  In this case, 'StartDate' - ('StartDate' / (3600*24)) would return datetime value reduced by ordinal day in epoch (in other words, number of days since 1970/01/01), while 'StartDate' - ('StartDate' % (3600*24)) would return datetime value with time value set to 0:00:00 (or 12:00 AM).

                       

                       

                      As to how to enter suggested SQL SELECT fragment, it is probably impossible to use the ones I mention in Simple formula type because you need to use modulo operator (%) and it cannot be entered manually; icon with the same symbol stands for average so it doesn't fit either.

                       

                      To find out actual table.columnname, simply (temporarily) add attribute you wish to identify to first column, refresh report and click on <View SQL> -- SQL Statement will start with something like SELECT DISTINCT and actual table and columnname of attribute you're trying to identify will be listed right after it.  Copy that line, without comma at the end, to clipboard or simple textual document and finally remove temporary column because attribute has been identified.  Later paste the value you've copied into Freehand SQL where necessary (e.g. replace `WOI:WorkOrder`.`Estimated Resolution Date` with value you've copied for Target Date, and `WOI:WorkOrder`.`Completed Date` with value copied for Start Date).  Also, you cannot reference calculated fields in Freehand SQL by name, but you can use this technique to copy its definition and paste it where necessary, regardless if (original) calculated field is defined with Simple, Pre-defined, or Freehand SQL formula.

                       

                      For a quick (and final) result, you only need to use the third calculated field in its expanded SQL SELECT fragment -- the first two are used to better illustrate what I'm suggesting and would probably only be used to test report during preparation.

                      2 of 2 people found this helpful
                      • 9. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
                        Devin Lindsay

                        Thank you Sinisa,
                        This solution should work.  One last question for you though: Where is this <View SQL> button that I would click on?  I am using the bmc Remedy Smart Reporting web app and can't seem to find it anywhere.  I wonder if this was available in a previous version or possibly that it needs to be enabled by an administrator or something.

                        • 10. Re: Calculated field using Date function on timestamp. Creating a calculated field on first field, it still considers time. How can I change that?
                          Sinisa Mikor

                          Clicking on <View SQL> while editing report is the equivalent of clicking on Details icon and looking at SQL Statement when viewing active reports, just without the need to activate report, refresh it, and click twice more; it should be displayed in the lower right corner, but only when results are displayed in "main" window and that's why I mentioned that you need to refresh report (to have them displayed) before looking at SQL statement.