2 Replies Latest reply on Apr 7, 2011 9:30 AM by lianne.redding

    Database date/times to always be local time

    Apprentice

      Hi,

       

      It has come to light that we are suffering from this long standing issue of dates and times being recorded in the database an hour out when we are on UK summer time.

       

      This subsequently put all of our reports out since we use MS Reporting Services and query the database directly. I have no intention of re-writing the reports to account for the 6 month time change.

       

      Is it possible to always have ITBM record the dates and times in the correct time zone? In our case, always UK time, and never anything else.

       

      This is quite an urgent matter for us, so if anyone has any suggestions I would be really grateful.

       

      To give some background aruond what has already been done:

      - All users have the correct timezone in ITBM

      - The SQL user is set to British English

      - All reports are set to en-GB

       

      THanks

      Dan

        • 1. Re: Database date/times to always be local time
          Stu McNeill Employee

          Hi Daniel,

           

          To cope with daylight savings and for our friends abroad most DateTime attributes in Service Desk are stored in UTC then coverted to your local time zone by the clients.  You can see which attributes are stored in UTC by looking at the "Adjust for Time Zone" property in Object Designer but I can tell you now 99% of your attributes will be.

           

          Of course this does create some extra work when reporting directly from the database.  We currently provide an addon to Cyrstal Reports known as a CRUFL to do the converstion from UTC to local time (or Crystal 11 onwards has a built-in function for this) but don't provide anything for Microsoft Reporting Services.

           

          However, a quick Google led me to this discussion which suggests there is a built-in function you can use:  http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/500448a3-bf58-44ab-8572-81becd67d8b8/.  I'm not familiar with how to use expressions in Microsoft Reporting but it should be fairly straight forward.

           

          I hope that clarifies why we use UTC and more improtantly how you can convert it to local time in your reports.

          • 2. Re: Database date/times to always be local time
            lianne.redding Apprentice

            Hi Stu - can you say what crufl it is in version pre 11?  I thought i'd only be able to do this from 11 onwards so glad to hear there may be something for earlier crystal versions    I was really pleased to find out that someone here had simply added one hour to the creation date ALWAYS so you can imagine the mess that arose!!!