3 Replies Latest reply on Oct 5, 2009 5:01 AM by JJJ

    Crystal Reports "Last Update" time conversion

    Jamie Cannon ITSMMVPGroup

      Is there a simple way to convert the "Last Update" time field in Crystal Reports to EST?  I am pulling straight from the database and it is saved in GMT and it doesn't really help me a lot.  Is there a simple way on a 64-bit machine to make this conversion?

        • 1. Re: Crystal Reports "Last Update" time conversion
          masterpetz ITSMMVPGroup

          Hi Jamie,

           

          you could try something like this in Crystal Reports:

           

          ShiftDateTime ({pm_process.pm_last_update},",0,GMT",",300,EST")

           

          Best regards

          Christian

          • 2. Re: Crystal Reports "Last Update" time conversion
            Stu McNeill Employee

            The ShiftDateTime function is great, just be warned that its only available from Crystal Reports 11 onwards.  If you run a report from within Console this will still use the Crystal 10 runtime.

             

            Also its worth mentioning the ShiftDateTime function can convert from UTC to "your local time" (whatever that may be) using:

             

            ShiftDate((table.column), "UTC,0", "")

            • 3. Re: Crystal Reports "Last Update" time conversion
              JJJ Employee

              If you need to run the report in Console, then you could try the following formula which should replicate the shiftdatetime function:-

               

              datetimeVar adate := {pm_process.pm_last_update};

               

              datetimeVar dlsstart := datetime( year(adate), 3, 1, 7, 0, 0);
              datetimeVar dlsend := datetime( year(adate), 11, 1, 7, 0, 0);

               

              dlsstart := dateadd("d", 7, dateadd("d", 7 - dayofweek(dlsstart, crmonday) , dlsstart));
              dlsend := dateadd("d", 7 - dayofweek(dlsend, crmonday), dlsend);

               

              if adate in dlsstart to dlsend
                  then dateadd("h", -4, adate)
                  else dateadd("h", -5, adate)

               

               

              The one drawback with this formula is that it will only change the date/time to the EST timezone, if you have other users in different timezones then they will not see the date/time local to them, whereas the shiftdatetime function (as per Stu's reply) would cater for users in any timezone:-

               

                   shiftdatetime({pm_process.pm_last_update}, "UTC,0", "")

               

               

              Also should the point in time that Daylight Saving start and end change, the formula would need amending to cater.