Skip navigation
Currently Being Moderated

Crystal 10 or Earlier only - Convert date time to local time - UK ITBM Crystal reports

VERSION 3  Click to view document history
Created on: Nov 12, 2008 4:39 PM by CCrossfield - Last Modified:  Jul 14, 2010 5:38 AM by KarenPeacock
Question

Dates and times are stored in the ITBM database in UTC (GMT).  If you would like to convert the date time to local time then one method is to use the formula below.

Answer

Open the required report in Crystal and create the formula below.

 

EXAMPLE 1

 

The example below is for an offset of 1 hour to be applied to the {pm_process.pm_creation_date} during certain times of the year - I've used British Summer Time as my example.  The date time field on the report needs to be removed and then replaced with this formula.  If your offset from UTC/GMT doesn't change throughout the year then you only need to include one line of the formula (I've given an example further down)

 

IMPORTANT please:  I have set this up until 2011 based on information publised on British Summer Time  starting and finishing.  However, I undestand that it is possible that these can change so please check at the start of each year when you also setup your public holidays for the year.

 

if {pm_process.pm_creation_date} in CDateTime (2007, 03, 25, 01, 00, 00) to CDateTime (2007, 10, 28, 01, 00, 00)

then

dateadd("h", 1, {pm_process.pm_creation_date})

else

if {pm_process.pm_creation_date} in CDateTime (2008, 03, 30, 01, 00, 00) to CDateTime (2008, 10, 26, 01, 00, 00)

then

dateadd("h", 1, {pm_process.pm_creation_date})

else

if {pm_process.pm_creation_date} in CDateTime (2009, 03, 29, 01, 00, 00) to CDateTime (2009, 10, 25, 01, 00, 00)

then

dateadd("h", 1, {pm_process.pm_creation_date})

else

if {pm_process.pm_creation_date} in CDateTime (2010, 03, 28, 01, 00, 00) to CDateTime (2010, 10, 31, 01, 00, 00)

then

dateadd("h", 1, {pm_process.pm_creation_date})

else

if {pm_process.pm_creation_date} in CDateTime (2011, 03, 27, 01, 00, 00) to CDateTime (2011, 10, 30, 01, 00, 00)

then

dateadd("h", 1, {pm_process.pm_creation_date})

else

{pm_process.pm_creation_date}

 

EXAMPLE 2 - FOR SET OFFSET OF +7 HOURS (offset doesn't change through different times of the year)

 

dateadd("h", 7, {pm_process.pm_creation_date})

 

Environment

ITBM all versions

 

NOTE: There is also a free .dll that we've found which converts to local time and I've created an article about this which you can access here.  However, the drawback is this was designed for crystal 9 and although we can get it to work on some higher versions of crystal we haven't been able to get it working on 64 bit or windows 2008 as yet.  As its not our .dll then we can only provide limited support but will help if we can.

Comments (5)
LANDESK Community powered by Jive SBS® 4.5.7.1  |  Legal Notices  |  Privacy Policy  |  Icon 

TweeterOn Twitter  |  Icon FacebookOn Facebook © 2007 LANDESK Software