BusinessObject: Dynamic Timezone Conversion

Product: BusinessObject
Module: Crystal Report designer

I have see many people posting about timezone conversion related questions, and found many people are proposing a static timezone conversion, e.g. -1 hour for EST and CST.

This solution does not work for people in none USA timezone, for example, server is in USA, but user who view the report is in London, where both countries have different DST

Following solution auto detect server and user timezone, and adjust the time accordingly, instead of using hard coded time different. For illustration purpose, build-in function CurrentDateTime is used, but it can be replaced with column name to convert the time to correct timezone

1. Convert Crystal Report server timezone to user's timezone

ShiftDateTime ( CurrentDateTime, PrintTimeZone, CurrentCEUserTimeZone)

2. Convert UK timezone to Sweden's, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0,BST", "CET,-60,CEST")

3. Convert UTC time to server's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0", PrintTimeZone)

4. Convert UTC time to user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0",  CurrentCEUserTimeZone )

5. Convert server's timezone to UTC time

ShiftDateTime ( CurrentDateTime, PrintTimeZone, "UTC,0")

6. Convert user's timezone to UTC time

ShiftDateTime ( CurrentDateTime, CurrentCEUserTimeZone, "UTC,0")

7. Convert US Eastern timezone to Pacific timezone, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "EST,0,EDT", "PST,60,PDT")

8. Convert UTC time to Eastern timezone, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0", "PST,480,PDT")

9. Convert based on timezone defined in user table stored in database

This make use of Oracle functions instead of Crystal Report. So create a SQL Expression in Crystal Report with following value

( Select From_TZ( OrderTimestamp , GlobalDataTimeZone) AT Time Zone TargetDataTimeZone As TzValue From Dual )


10. Same as above, but use client's timezone

( Select From_TZ( OrderTimestamp , GlobalDataTimeZone) AT Time Zone SESSIONTIMEZONE As TzValue From Dual )



Note on Syntax:

ShiftDateTime ( [inputDateTime], [fromTimezone] , [toTimezone] )

where fromTimezone and toTimezone has following syntax

"Standard, Offset, DST, [offset in minute];[DST start date]/[and time],[DST end date]/[and time]"

"" (empty string) will use server's timezone defined in OS

CurrentCEUserTimeZone

Return the timezone (string) of the user viewing the report

PrintTimeZone

Return the timezone (string) of the BusinessObject server

The time zone string must be valid Crystal Report value. I recommend to use CurrentCEUserTimeZone and PrintTimeZone function to see the value on the report while designing the report, and change the OS's timezone to test it