2 Replies Latest reply on Dec 19, 2012 9:23 AM by aranslam

    Crystal Reports Question RE: Date/Time fields

    Rookie

      Hello,

       

      We had some help getting some crystal reports designed and one of them involved using a date range to return a number of incidents within that period.  We simply just choose the start date and end date (the time is not an option).  The problem we are having is that it will return incidents from the previous day from 4:00 PM PST onward and likewise stop reporting at 4:00 PM PST the last day (we are in PST).  So if I run the report to see these particular incidents with a start and end date of  12/13/12 I will get incidents returned to me from 12/12/12 @ 4:00 PM PST through 12/13/12 @ 4:00 PM PST.  The only things I  could find referencing date time issues were adjusting the shift.  We already have that accounted for in the Crystal reports and that is working properly.  The issue seems to be that a day for us in the report seems to run from 4:00 PM PST to 4:00 PM PST rather than midnight to midnight.

       

      We are running 7.5 SP1

       

      Any help would be appreciated.

       

      Thanks,

      Anthony

        • 1. Re: Crystal Reports Question RE: Date/Time fields
          karenpeacock SupportEmployee

          Hi Anthony

           

          Could it be that the date time fields displaying on the report have been converted to show your local time but when you are picking the date range for the report this is applying to a field which is in UTC/GMT? 

           

          Correcting this has to be done carefully because you can't simply point your report parameters to the converted local date time formula field instead.  I did this in the past, and it was found to cause performance issues because Crystal has to go and get every record in the database then convert the timezone in order to first work out whether or not the record is needed for the report or not.

           

          I am not sure if anyone has come up with any better solutions but I think you could take the parameter fields as you select them when running the report and convert them to UTC/GMT using a formula and then apply this to your UTC/GMT date field.

           

          Regards

          Karen

          • 2. Re: Crystal Reports Question RE: Date/Time fields
            Rookie

            Hi Karen,

             

            Thanks for the reply.  I think you were able to articulate what I was unable too.  This is exactly what is happening.  We are reporting off of the resolution date field and when I look in the SQL DB the dates/times show up as GMT time.  So the report adjusts the times to show PST properly but looks at a "day" as 12:00 GMT to 12:00 GMT because that has not been adjusted.

             

            Unfortunately for me, I have no discernable skills with Crystal Reports and was hoping someone else had a similar issue they ran into and could provide an example I could modify for my own purposes.

             

            Thanks,

            Anthony